MySQL

This article contains various common tasks involving MySQL and MariaDB.

Installation

MariaDB 10 on CentOS 7

The system default version of MariaDB is 5.5 on CentOS 7. To use the latest version of MariaDB, use the official MariaDB repo. Create a new yum repo by editing /etc/yum.repos.d/MariaDB.repo with the following:

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

Then install the MariaDB package.

Docker

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

These instructions can be used with MariaDB.

Create/Drop MySQL Account and Modify Privileges

# mysql
> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
> GRANT <permission type> ON <database>.<table> TO '<username>'@'<host>';
> REVOKE <permission type> ON <database>.<table> FROM '<username>'@'<host>';
> DROP USER 'new_user'@'localhost';
> FLUSH PRIVILEGES;

Permissions can be one of the following:

USAGE	Gives the user permission to log in to the MySQL server(given by default when creating a new user)
SELECT	Gives the user permission to use the select command to fetch data from tables
INSERT	Gives the user permission to add new rows into tables
UPDATE	Gives the user permission to modify the existing rows in tables
DELETE	Gives the user permission to delete existing rows from tables
CREATE	Gives the user permission to create new tables or databases
DROP	Gives the user permission to remove existing tables or databases
ALL PRIVILEGES	Gives the user permission to have unrestricted access on a database or the whole system(by using an asterisk in the database position)
GRANT OPTION	Gives the user permission to grant or remove other users’ permissions

Reset MySQL Root Password

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

## 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

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

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

• https://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

Restoring SQL Dump from cPanel Backups

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

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

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

Loading Timezone Data

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.

Show Table Sizes

To show the sizes of each table in a database, run:

> SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)" FROM information_schema.TABLES
WHERE table_schema = "slurm_acct_db" 
ORDER BY (data_length + index_length) DESC;
+-------------------------+-----------+
| Table                   | Size (MB) |
+-------------------------+-----------+
| step_table              |   6586.00 |
| job_table               |   5679.42 |
| assoc_usage_hour_table  |    180.89 |
| event_table             |     21.55 |
| assoc_usage_day_table   |     15.55 |

...

Troubleshooting

Issue with Restoring Large Dumps

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

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

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.

Incorrect definition of table mysql.column_stats

An instance of MariaDB within a container started using up a lot of CPU and throwing the following errors:

2022-08-13 16:03:36 754 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2022-08-13 16:03:36 754 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).
2022-08-13 16:03:36 754 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'hist_type' at position 9 to have type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), found type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB').
2022-08-13 16:03:36 754 [ERROR] Incorrect definition of table mysql.column_stats: expected column 'histogram' at position 10 to have type longblob, found type varbinary(255).

Since the container pulled the latest MariaDB image, it's likely an upgrade occurred but not all the data was properly migrated resulting in a schema mismatch. The solution here is to run the mysql_upgrade command which will go through each database and apply an upgrade if required.

## If using a container, enter the container with `docker exec -ti $ContainerID sh`
# mysql_upgrade --user=root --password
Enter password:
Major version upgrade detected from 10.5.9-MariaDB to 10.8.3-MariaDB. Check required!
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
...
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
mysql.user                                         OK
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Processing databases
...
performance_schema
sys
sys.sys_config                                     OK
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK

This is a bug in MariaDB 10.8.3 (https://jira.mariadb.org/browse/MDEV-28866). Upgrade to 10.8.4 or higher to fix.

If this is still affecting you, then your next best course of action is to dump the database and re-import it in a clean container. This is what I had to do to fix a NextCloud database.

Additionally, it might be helpful to include the MARIADB_AUTO_UPGRADE=1 environment variable in the container so that the database schema is automatically upgraded when a newer version of the database is used.