This article contains various common tasks involving MySQL and MariaDB.


Docker[edit]

When using the MariaDB docker image, variables can be passed to the database server by overriding the command.

An example docker-compose.yml file for the database entry could look like this:

image: mariadb:latest
environment:
  - MYSQL_ROOT_PASSWORD=foobar
command: mysqld --innodb-buffer-pool-size=256M

Tasks[edit]

These instructions can be used with MariaDB.

Reset MySQL Root Password[edit]

To reset the root password, restart MySQL with the --skip-grant-tables option. This starts MySQL without user accounts allowing you to set the root password.

# service mysql stop
# mysqld_safe --skip-grant-tables

# mysql
> update mysql.user set password=PASSWORD('newpassword') WHERE user='root';
> quit

# service mysql restart

See Also: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Change MySQL Account Password[edit]

## Before MySQL 5.7.5 / MariaDB 10.1.20
> SET PASSWORD FOR 'user-name'@'localhost' = PASSWORD('NEW_USER_PASSWORD');

## After MySQL 5.7.5 / MariaDB 10.1.20
> ALTER USER 'user-name'@'localhost' IDENTIFIED BY 'NEW_USER_PASSWORD';

## Or manually modifying the mysql users table
> UPDATE mysql.user SET authentication_string = PASSWORD('NEW_USER_PASSWORD') WHERE User = 'user-name' AND Host = 'localhost';

## In any of the above cases, flush privileges after to apply
> FLUSH PRIVILEGES;

.my.cnf[edit]

You can make the mysql client not prompt for username or password if it is given in a ~/.my.cnf file.

[client]
password="xxxxxxx"
user=root

Binary Log Management[edit]

If you have binary logging enabled in MySQL, you may notice that the binary logs produced by MySQL can grow quite large:

bash-3.2# ls -al /opt/mysql/var/
total 16240180
drwx------  5 mysql root        4096 Sep 30 15:33 .
drwxr-xr-x 11 mysql mysql       4096 Sep 30 15:33 ..
drwx------  2 mysql root        4096 Aug 15 02:15 mysql
-rw-rw----  1 mysql mysql      19103 Aug 15 02:15 mysql-bin.000001
-rw-rw----  1 mysql mysql     729901 Aug 15 02:15 mysql-bin.000002
-rw-rw----  1 mysql mysql        125 Aug 15 02:15 mysql-bin.000003
-rw-rw----  1 mysql mysql        106 Aug 15 02:15 mysql-bin.000004
-rw-rw----  1 mysql mysql 1074557984 Sep 18 17:59 mysql-bin.000005
-rw-rw----  1 mysql mysql 1074199027 Sep 18 18:52 mysql-bin.000006
-rw-rw----  1 mysql mysql   47809403 Sep 18 18:53 mysql-bin.000007
-rw-rw----  1 mysql mysql 1077169445 Sep 18 18:55 mysql-bin.000008
-rw-rw----  1 mysql mysql 1074419095 Sep 18 18:57 mysql-bin.000009
-rw-rw----  1 mysql mysql 1073742057 Sep 18 21:41 mysql-bin.000010
-rw-rw----  1 mysql mysql 1073742056 Sep 19 03:58 mysql-bin.000011
-rw-rw----  1 mysql mysql 1073781146 Sep 19 10:53 mysql-bin.000012
-rw-rw----  1 mysql mysql 1073742068 Sep 20 01:15 mysql-bin.000013
-rw-rw----  1 mysql mysql 1073754553 Sep 22 18:22 mysql-bin.000014
-rw-rw----  1 mysql mysql  158604158 Sep 25 18:33 mysql-bin.000015
-rw-rw----  1 mysql mysql        125 Sep 25 18:36 mysql-bin.000016
-rw-rw----  1 mysql mysql        125 Sep 25 19:21 mysql-bin.000017
-rw-rw----  1 mysql mysql        125 Sep 25 19:47 mysql-bin.000018
-rw-rw----  1 mysql mysql    5212790 Sep 25 20:34 mysql-bin.000019
-rw-rw----  1 mysql mysql 1073741925 Sep 26 01:43 mysql-bin.000020
-rw-rw----  1 mysql mysql 1073741893 Sep 26 03:43 mysql-bin.000021
-rw-rw----  1 mysql mysql 1073741937 Sep 26 14:24 mysql-bin.000022
-rw-rw----  1 mysql mysql 1073741938 Sep 27 08:38 mysql-bin.000023
-rw-rw----  1 mysql mysql 1073746311 Sep 28 20:43 mysql-bin.000024
-rw-rw----  1 mysql mysql  875141163 Sep 30 15:22 mysql-bin.000025
-rw-rw----  1 mysql mysql  488120905 Oct  4 01:52 mysql-bin.000026
-rw-rw----  1 mysql mysql        494 Sep 30 15:33 mysql-bin.index
...
bash-3.2# du -ch mysql-bin*

This is particularly problematic on smaller systems like a VPS with limited disk space. To manually clear these logs, run the PURGE BINARY LOGS command in MySQL.

mysql> show binary logs;

+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000001 |      19103 |
| mysql-bin.000002 |     729901 |
| mysql-bin.000003 |        125 |
| mysql-bin.000004 |        106 |
| mysql-bin.000005 | 1074557984 |
| mysql-bin.000006 | 1074199027 |
| mysql-bin.000007 |   47809403 |
| mysql-bin.000008 | 1077169445 |
| mysql-bin.000009 | 1074419095 |
| mysql-bin.000010 | 1073742057 |
| mysql-bin.000011 | 1073742056 |
| mysql-bin.000012 | 1073781146 |
| mysql-bin.000013 | 1073742068 |
| mysql-bin.000014 | 1073754553 |
| mysql-bin.000015 |  158604158 |
| mysql-bin.000016 |        125 |
| mysql-bin.000017 |        125 |
| mysql-bin.000018 |        125 |
| mysql-bin.000019 |    5212790 |
| mysql-bin.000020 | 1073741925 |
| mysql-bin.000021 | 1073741893 |
| mysql-bin.000022 | 1073741937 |
| mysql-bin.000023 | 1073741938 |
| mysql-bin.000024 | 1073746311 |
| mysql-bin.000025 |  875141163 |
| mysql-bin.000026 |  492014457 |
+------------------+------------+
26 rows in set (0.00 sec)

mysql> purge binary logs before '2011-08-01';
Query OK, 0 rows affected (1.61 sec)

Rather than specifying the date, it's also possible to purge all logs before a specified filename:

mysql> purge binary logs to 'mysql-bin.000005';
Query OK, 0 rows affected (6.25 sec)

mysql> show binary logs;

+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000005 | 1074557984 |
| mysql-bin.000006 | 1074199027 |
| mysql-bin.000007 |   47809403 |
| mysql-bin.000008 | 1077169445 |
| mysql-bin.000009 | 1074419095 |
| mysql-bin.000010 | 1073742057 |
| mysql-bin.000011 | 1073742056 |
| mysql-bin.000012 | 1073781146 |
| mysql-bin.000013 | 1073742068 |
| mysql-bin.000014 | 1073754553 |
| mysql-bin.000015 |  158604158 |
| mysql-bin.000016 |        125 |
| mysql-bin.000017 |        125 |
| mysql-bin.000018 |        125 |
| mysql-bin.000019 |    5212790 |
| mysql-bin.000020 | 1073741925 |
| mysql-bin.000021 | 1073741893 |
| mysql-bin.000022 | 1073741937 |
| mysql-bin.000023 | 1073741938 |
| mysql-bin.000024 | 1073746311 |
| mysql-bin.000025 |  875141163 |
| mysql-bin.000026 |  495852653 |
+------------------+------------+

22 rows in set (0.00 sec)

By executing the command above, all logs up to 000005 were deleted.

To disable binary logging if your server is not replicating itself by editing removing

log-bin=mysql-bin

in /etc/my.cnf

See Also[edit]

Restoring SQL Dump from cPanel Backups[edit]

To restore the .sql dump in a tarball such as a cpanel backup file do something like:

# zcat account.tar.gz

Split Database Dumps to Individual Table Dumps[edit]

Use the csplit command to split database dumps at the '-- Table Structure' line.

# csplit -s -ftable $1 "/-- Table structure for table/" {*}

Loading Timezone Data[edit]

Clean database installations or using the official mariadb:latest container image will not come with timezone data set in the database.

Timezone data can be imported with the following command:

# mysql_tzinfo_to_sql /usr/share/zoneinfo

If Timezone data is not available, certain PHP scripts might break with a Couldn't change session tz_session! error.

Troubleshooting[edit]

Issue with Restoring Large Dumps[edit]

When restoring a large dump, you may see:

cat dump.sql

If the import is dumping in a large blob of data, you will need to increase the max_allowed_packet in my.cnf.

InnoDB Sequential Autoincrement on Duplicate Key Insert[edit]

You may notice that the auto increment value increments even on a duplicate key insertion. To make InnoDB act similar to MyISAM, put the following line under the [mysqld] section in /etc/my.cnf

innodb_autoinc_lock_mode = 0


Recovering Missing Table[edit]

One of the cPanel accounts had a table completely missing when the server disk filled up.

On the MySQL console:

mysql> select * from database.wp_wfNet404s;
ERROR 1146 (42S02): Table 'database.wp_wfNet404s' doesn't exist

mysql> repair table database.wp_wfNet404s;

+-----------------------------+--------+----------+---------------------------------------------------+
| Table                       | Op     | Msg_type | Msg_text                                          |
+-----------------------------+--------+----------+---------------------------------------------------+
| database.wp_wfNet404s       | repair | Error    | Table 'database.wp_wfNet404s' doesn't exist       |
| database.wp_wfNet404s       | repair | status   | Operation failed                                  |
+-----------------------------+--------+----------+---------------------------------------------------+

2 rows in set (0.00 sec)

Listing the database files showed the INNODB table files completely missing:

# cd /var/lib/mysql/database
# ls -al
-rw-rw----   1 mysql mysql  180224 May 30 01:05 wp_wfLogins.ibd
-rw-rw----   1 mysql mysql    8618 Dec  7  2016 wp_wfNet404s.frm
-rw-rw----   1 mysql mysql    8754 Jun 14 17:31 wp_wfNotifications.frm

It doesn't look like I can get MySQL to just rebuild the database with no data. The only way I got this working again was to restore the database and copy the missing table files back over. (If the table isn't critical, you can copy it from another database if you know it has the same schema).

# cp ../database2/wp_wfNet404s* .
# ls -al
-rw-rw----   1 mysql mysql    8618 Jun 27 17:02 wp_wfNet404s.frm
-rw-r-----   1 mysql mysql     172 Jun 27 17:02 wp_wfNet404s.MYD
-rw-r-----   1 mysql mysql    3072 Jun 27 17:02 wp_wfNet404s.MYI

After restarting the database server, it seemed to be happy again.

Enable Dark Mode!