MySQL: Difference between revisions

From Leo's Notes
This page was last edited on 18 August 2019, at 22:31.
← Older editNewer edit →
VisualWikitext
No edit summary
No edit summary
Line 1: Line 1:
This article contains various common tasks involving MySQL and MariaDB.
This article contains various common tasks involving MySQL and MariaDB.


== Docker ==
When using the MariaDB docker image, variables can be passed to the database server by overriding the command.
An example {{code|docker-compose.yml}} file for the database entry could look like this:
{{highlight|lang=text|code=
image: mariadb:latest
environment:
  - MYSQL_ROOT_PASSWORD=foobar
command: mysqld --innodb-buffer-pool-size=256M
}}
== Tasks ==
{{Info|
{{Info|
These instructions can be used with MariaDB.
These instructions can be used with MariaDB.
}}
}}


== Reset Mysql Root Password ==
=== Reset MySQL Root Password ===
To reset the root password, restart mysql with the {{code|--skip-grant-tables}} option. This starts MySQL without user accounts allowing you to set the root password.
To reset the root password, restart MySQL with the {{code|--skip-grant-tables}} option. This starts MySQL without user accounts allowing you to set the root password.


{{highlight|lang=terminal|code=  
{{highlight|lang=terminal|code=  
service mysql stop
# service mysql stop
mysqld_safe --skip-grant-tables
# mysqld_safe --skip-grant-tables


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


service mysql restart
# service mysql restart
}}
}}


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


== Binary Logs ==
=== 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:
If you have binary logging enabled in MySQL, you may notice that the binary logs produced by MySQL can grow quite large:


 
{{highlight|lang=terminal|code=
<syntaxhighlight lang="text">
bash-3.2# ls -al /opt/mysql/var/
bash-3.2# ls -al /opt/mysql/var/
total 16240180
total 16240180
Line 61: Line 73:
bash-3.2# du -ch mysql-bin*|grep total
bash-3.2# du -ch mysql-bin*|grep total
16G    total
16G    total
</syntaxhighlight>
}}


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


<syntaxhighlight lang="text">
{{highlight|lang=terminal|code=
mysql> show binary logs;
mysql> show binary logs;
+------------------+------------+
+------------------+------------+
Line 101: Line 113:
mysql> purge binary logs before '2011-08-01';
mysql> purge binary logs before '2011-08-01';
Query OK, 0 rows affected (1.61 sec)
Query OK, 0 rows affected (1.61 sec)
</syntaxhighlight>
}}


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


<syntaxhighlight lang="text">
{{highlight|lang=terminal|code=
mysql> purge binary logs to 'mysql-bin.000005';
mysql> purge binary logs to 'mysql-bin.000005';
Query OK, 0 rows affected (6.25 sec)
Query OK, 0 rows affected (6.25 sec)
Line 137: Line 149:
+------------------+------------+
+------------------+------------+
22 rows in set (0.00 sec)
22 rows in set (0.00 sec)
</syntaxhighlight>
}}


By executing the command above, all logs up to 000005 were deleted.
By executing the command above, all logs up to 000005 were deleted.
Line 143: Line 155:
To disable binary logging if your server is not replicating itself by editing removing {{highlight|lang=terminal|code= log-bin=mysql-bin}} in {{code|/etc/my.cnf}}
To disable binary logging if your server is not replicating itself by editing removing {{highlight|lang=terminal|code= log-bin=mysql-bin}} in {{code|/etc/my.cnf}}


=== See Also ===
==== See Also ====
* https://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
* https://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html


== Restoring SQL Dump from cPanel Backups ==
=== Restoring SQL Dump from cPanel Backups ===
To restore the .sql dump in a tarball such as a cpanel backup file do something like:
To restore the .sql dump in a tarball such as a cpanel backup file do something like:
  zcat account.tar.gz|tar -xvf - account/mysql/account_db.sql -O | mysql -u root -p account_db
  zcat account.tar.gz|tar -xvf - account/mysql/account_db.sql -O | mysql -u root -p account_db


== Split Database Dumps to Individual Table Dumps ==
=== Split Database Dumps to Individual Table Dumps ===
Use the csplit command to split database dumps at the '-- Table Structure' line.
Use the csplit command to split database dumps at the '-- Table Structure' line.
  csplit -s -ftable $1 "/-- Table structure for table/" {*}
  csplit -s -ftable $1 "/-- Table structure for table/" {*}


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


Revision as of 22:31, 18 August 2019

This article contains various common tasks involving MySQL and MariaDB.


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.


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

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;
+------------------+------------+

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;
+------------------+------------+

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

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|tar -xvf - account/mysql/account_db.sql -O | mysql -u root -p account_db

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.

Troubleshooting

Issue with Restoring Large Dumps

When restoring a large dump, you may see: 

cat dump.sql | mysql -u root -p database
MySql server has gone away

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;
+-----------------------------+--------+----------+---------------------------------------------------+

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.

Linux

Recent Changes

Recent Changes under Linux

Recent Changes under Guides

Ad-Free + Tracker-Free

I respect your privacy. This site does not serve ads, use trackers/analytics, or load resources from 3rd party websites.

Cookies may be created by MediaWiki but will contain no user identifiable information.