MySQL

Installation

Install as usual for your distribution.

For Debian, this means:

 $ sudo apt-get install phpmyadmin

Setting passwords

After installation of MySQL, set root password as follows:

  $ mysqladmin -u root password NEWPASSWORD

If you prefer the mysql client:

  $ mysql -u root
  mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret');

Resetting the MySQL root password

The following procedure works on Debian Linux, but with some adjustments should work on any Linux distribution. You'll need sudo rights, or the root UNIX password.

Create a file named /tmp/reset.sql with the following lines, and adjust the password:

 UPDATE mysql.user SET Password=PASSWORD('my_new_password') WHERE User='root';
 FLUSH PRIVILEGES;

Stop the MySQL server:

 $ sudo /etc/init.d/mysql stop

Start the server manually, then wait a couple of seconds. The script will then be executed and you can press CTRL-C to stop it.

 $ sudo /usr/bin/mysqld_safe --user=mysql --init-file=/tmp/reset.sql
 nohup: ignoring input and redirecting stderr to stdout
 Starting mysqld daemon with databases from /var/lib/mysql
 mysqld_safe[25272]: started
 ^C
 STOPPING server from pid file /var/run/mysqld/mysqld.pid
 mysqld_safe[25290]: ended

Start the server once more, now in the normal way once more.

 $ sudo /etc/init.d/mysql start

You should now be able to log in with the new password.

New generic database

  $ mysql -u root -p
  mysql> create database telis;
  mysql> grant all on telis.* to 'telis'@'localhost' identified by 'blahblah';
  mysql> show grants for telis;
  mysql> \q

Note: mysql doesn't report errors if you make a typo in the database name.

New database for phpMyAdmin

To create a new database and new user for phpMyAdmin:

  mysql> create database phpmyadmin;
  mysql> GRANT ALL PRIVILEGES ON phpmyadmin.*
         TO 'phpmyadmin'@'localhost'
         IDENTIFIED BY 'nwepassword' WITH GRANT OPTION;

Installing phpMyAdmin

Download from http://www.phpmyadmin.net/ and unpack in any directory that's accessible. After unpacking, make a directory for saving the configuration:

  $ mkdir config
  $ chmod 777 config

Run the setup by going to the phpMyAdmin page using the browser. Click on the setup link. Click 'add server'. Pay special attention to authentication and the database that was set above (tip: use "cookie"). Click 'add' and click 'save' in the configuration line. On the commandline, type:

  $ mv config/* .
  $ rm -rf config

Now go to the phpMyAdmin main page again. You should be prompted for a password.

The following configuration lines are very interesting to adapt:

  $cfg['MaxRows'] = 500;     // default 30, for easy browing
  $cfg['TextareaRows'] = 30; // default 6, for easy SQL query editing
  $cfg['LoginCookieValidity'] = 18000; // Five hours before auto-logout

Adding pmadb to existing phpMyAdmin installation

The pmadb is the phpMyAdmin database which stores the data for additional functionality like maintaining history, storing bookmarks to SQL queries, et cetera.

Sometimes you run into an installation where the pmadb wasn't created. For example Debian does this. To rectify this situation, create a database user 'pmadb'. Then locate the create_tables script somewhere on disk (for Debian, /usr/share/doc/phpmyadmin/scripts/create_tables_mysql_4_1_2+.sql.gz) and import it while logged in as root in phpMyAdmin. This will create the phpmyadmin database.

Then edit phpMyAdmin its config.inc.php file (for Debian, /etc/phpmyadmin/config.inc.php) and uncomment all configuration lines which refer to a pma_... table. Also uncomment and edit the controluser and controlpassword lines.

Reinstallation

Sometimes the tables are corrupted and you want to start again. With Debian, list all mysql packages first:

  $ dpkg -l 'mysql*'

Remove those with the --purge option:

  $ sudo sudo apt-get remove --purge mysql-client-5.0 mysql-common mysql-server-5.0

Also rename any existing directories containing config files or databases:

  $ cd /etc
  $ sudo mv mysql mysql.old
  $ cd /var/lib
  $ sudo mv mysql mysql.old

Now reinstall:

  $ sudo apt-get install mysql-client-5.0 mysql-common mysql-server-5.0

Reload databases and hand out privileges again.

mysqldump

Options:

  mysqldump -e -u username [ -h hostname ] -p databasename > dump.sql

Note that the user needs the Lock Table privilege.

To load the dump:

  mysql -u username [ -h hostname ] -p databasename < dump.sql

Dumping from prompt

To dump the output of a SQL statement from the mysql client prompt to a file, use the following syntax:

  SELECT emp_id, emp_name
  FROM   emps 
  INTO OUTFILE '/tmp/test.txt';

Note that this instructs the server to write the stuff to file, that's why it's easiest to dump to /tmp since the server has rights to write there. Check the MySQL manual on SELECT ... INTO OUTFILE.

Lots of options are available, for example:

  FIELDS TERMINATED BY ','
  ENCLOSED BY '"'
  LINES TERMINATED BY '\n'

Shrinking database

Because it's not possible to shrink an InnoDB database (the ibdata1 file), MySQL has an option to put each table in a separate file. This way, space is easily reclaimed by just dropping tables and manually deleting files. The only other workaround is to export all data, recreate a database and import again.

Put the following option in the [mysqld] section in /etc/mysql.cnf:

  innodb_file_per_table

This triggers a bug on Windows, so be careful.

Printing in hex

To print a blob column in hexadecimal output, do:

  SELECT hex( `data` )
  FROM `queue_send`
  WHERE id =856;

To convert numbers between bases, use conv(). For example to convert from hexadecimal to decimal:

  SELECT conv(mynumber, 16, 10);

From decimal to hexadecimal:

  SELECT conv(mynumber, 10, 16);

To display a fixed number of digits:

  SELECT lpad(  conv(mynumber, 10, 16),  8, '0x000000');

You could use the hex() function to print a number in hexadecimal format, it's important to explicitly tell MySQL that we're dealing with a number here. This is done with any operator, so to convert the decimal number 10 to hex, do:

  SELECT hex(10 + 0);

When not converting but typing statements (for instance typing an insert statement), it might be easier not to use convert.

Using a hex value for select

If you want to use a hexadecimal value in the WHERE clause, use conv() with a hex string as follows:

  SELECT *
  FROM   params
  WHERE  addr = conv('a0000', 16, 10)

Status information

To view status information from the commandline, use:

  $ mysqladmin -uroot -ppassword status

The number of threads means the number of connections. This number will always at least be 1, since mysqladmin itself makes a connection.

For more information, use "extended":

  $ mysqladmin -uroot -ppassword extended

Extra logging on Debian

To see extra logging (on queries and new connections) on Debian, edit /etc/init.d/mysql and go to line 112 or thereabout, where it says:

  /usr/bin/mysqld_safe > /dev/null 2>&1 &

Change that into:

  /usr/bin/mysqld_safe --log=/var/log/mysql.log > /dev/null 2>&1 &

Restart MySQL with a quick

  # /etc/init.d/mysql restart

Then use tail to view the log:

  # tail -f /var/log/mysql.log

Be sure to remove the extra logging parameter in /etc/init.d/mysql, since logging is fairly heavy.

Connections from outside

MySQL normally only accepts connections from the localhost, but sometimes it's easy to be able to connect from anywhere. To be sure this is the case, type:

 $ netstat --tcp --listen | grep mysql
 tcp        0      0 localhost.localdomain:mysql *:*       LISTEN

The localhost.localdomain:mysql means: a process is listening on the mysql interface, on the local interface. Thus, it's not accessible over the network.

Edit the file /etc/my.cnf (or alternatively /etc/mysql/my.cnf) and look for the following line and if it's present, comment it out.

 bind-address = 127.0.0.1

Then add the following line:

 bind-address = 0.0.0.0

Restart the server and type:

 $ netstat --tcp --listen | grep mysql
 tcp        0      0 *:mysql             *:*              LISTEN

The *:mysql means: a process is listening on the mysql interface, on all interfaces.