MySQL on your own server

• MySQL databases are stored in /var/lib/mysql/db
• phpMyAdmin – work with MySQL through a web interface

Installing from the Debian repositories

• Install the MySQL server:

# apt-get install mysql-server

• It starts automatically after installation. To start it manually, run:

# /etc/init.d/mysql start

• You can see how it started in /var/log/syslog. Connect to the server:

# mysql -u root mysql

• Change the administrator password and exit:

mysql> UPDATE user SET Password=PASSWORD('new_pass') WHERE user='root';
mysql> quit

• Reload the server:

# /etc/init.d/mysql reload

• Connect to the server again, this time with the password:

# mysql -u root -p mysql

Moving a database (backup and restore)

You can export a database with phpMyAdmin or mysqldump:

# mysqldump -u root -p DBName > mydb.sql

To export all databases, use the --all-databases option instead of DBName.

If mysqldump does not run, give the full path to it, which you can find with this command:

# find / -name mysqldump
/usr/local/mysql-3.23.54a-pc-linux-i686/bin/mysqldump

To import a database, first create an empty one — and, to be safe, drop the old one first:

# mysql -u root -p
mysql> drop database if exists DBName;
mysql> create database DBName;
mysql> quit

and then load the data into it:

# mysql -u root -p DBName < mydb.sql

If the MySQL root user has no password, the -p option is not needed; you can also give the password directly, without a space, for example -pPassWord. Next, make sure that the new database has the user that was used to access the database before. If it does not, the simple way to give a user all privileges is:

mysql> GRANT ALL PRIVILEGES ON dbName.* TO UserName@localhost IDENTIFIED BY 'UserPassword';

Moving users with their privileges

To move users automatically, you can use the pt-show-grants utility from the Percona Toolkit suite. pt-show-grants works with Percona Server, MariaDB and MySQL, in any version.

To create manually exactly the same user as on the old server, with the corresponding privileges, export the mysql database and pick out all the rows that relate to that user:

# mysqldump -u root -p mysql > mysqldb.sql

The file mysqldb.sql will contain something like this:

INSERT INTO db VALUES ('%','DBName','UserName','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO host VALUES ('localhost','UserName','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
INSERT INTO user VALUES ('localhost', 'UserName', '','N','N','N','N','N','N','N','N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0);

When moving from one MySQL version to another, bear in mind that the number of fields is likely to differ, so you will need to edit the INSERT command. The password hashing algorithm may also change. You can find out how many fields there are by running:

mysql> SHOW COLUMNS FROM db;

You can specify only the fields you are interested in, and the rest will take their default values:

INSERT INTO `user` (`Host`, `User`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`) VALUES ('localhost','stacc','Y','Y','Y','Y');

Load the database with the command:

# mysql -u root -p mysql < mysqldb.sql

Commands

OS commands

MySQL commands

- You can insert a record into a database from bash like this (after defining the variables, of course):

echo "INSERT INTO table_name (\`in\`,\`out\`) VALUES ('$delta_in', '$delta_out');" > $tmp_dir/temp.sql
`mysql -u$db_user -p$db_pass $db_name < $tmp_dir/temp.sql`

How to reset the MySQL root password

The procedure:

1. Stop the server
2. Start it in a mode that skips privilege checks
3. Log in as root without giving a password
4. Change the root password
5. Exit
6. Restart the server

The commands:

# /etc/init.d/mysqld stop
# mysqld_safe --skip-grant-tables &
# mysql -u root mysql
mysql> update user set Password=PASSWORD('new_password') where User='root';
mysql> quit
# /etc/init.d/mysqld restart

How to convert tables from MyISAM to InnoDB

• Find out which tables use the MyISAM engine

mysql> SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');
+------------+-----------------+--------+
| DbName     | TableName       | Engine |
+------------+-----------------+--------+
| my_db_name | searchindex     | MyISAM |
| my_db_name | sw__searchindex | MyISAM |
+------------+-----------------+--------+

• Convert these tables to InnoDB:

ALTER TABLE my_db_name.searchindex ENGINE = InnoDB;
ALTER TABLE my_db_name.sw__searchindex ENGINE = InnoDB;

How to find the size of a database and its tables

• From the operating system:

# du -h /var/lib/mysql

• From MySQL. Log in as the root user:

 mysql -u root -p

and run this command:

SELECT table_schema AS "Database name",
SUM(data_length + index_length) AS "Size in bytes"
FROM information_schema.TABLES
GROUP BY table_schema;

To show the size in megabytes, replace the second line with:

ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size in MB"

To find the size of all tables in all databases:

SELECT table_schema as `Database`, table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Debugging

By default, MySQL does not log the commands that are run on it. You may need this to debug applications that talk to it, for example when setting up Courier. You can turn logging on in the file /etc/init.d/mysql by commenting out the original program start in the start section and adding the --log option:

# /usr/bin/mysqld_safe > /dev/null 2>&1 &
/usr/bin/mysqld_safe --log &

Troubleshooting

ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.12' (111)

Cause: requests are not reaching the MySQL server. There can be many reasons.

Solution: in /etc/mysql/my.cnf change the bind-address setting from the default 127.0.0.1 to the IP address of the interface — for example, on an internal network:

bind-address = 10.0.0.12

Links

• Official MySQL documentation. The name of each HTML file is the command it describes, which makes it quick to find the page for the command you need.