MySQL

From Leo's Notes
Revision as of 05:05, 29 March 2015 by Leo (talk | contribs)
This page was last edited on 29 March 2015, at 05:05.


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 Logs

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*|grep total
16G     total

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

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

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

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.

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/" {*}
Linux