MySQL bin logs can take disk space - and if not off-loading to archive server - can hog up space quickly - esp on a VPS or cloud where paying for diskspace...
Update: created a script that does this now: purgebinarylogs
Note: the script uses .my.cnf file for login info - please make sure to set that up/test first..
Also can see here what drupal tables I ignore - note I am not running any ecommerce nor high volume make my living off drupal sites - else I'd be writing about something else.. :)
I have a SSH tunnel setup - TCP takes care of keeping the data (mysql binlogs) pretty well intact - something that mysql binlogs do not have - integrity checking. Also by using the SSH tunnels, can keep the firewall shut.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: slave_user
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: bin.000031
Read_Master_Log_Pos: 65660958
Relay_Log_File: relay.000799
Relay_Log_Pos: 4698187
Relay_Master_Log_File: bin.000031
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: userwww.watch%,userwww.temp%,mysql.%,userdev.watch%,userdev.temp%,usertest.watch%, usertest.temp%,userwww2.watch%,userwww2.temp%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 65660958
Relay_Log_Space: 4698187
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Slave IO, Slave SQL = Yes, Seconds_Behind_Master: 0 - we are good.
root@host /var/log/mysql # ls -ltr total 2346716 -rw-rw---- 1 mysql mysql 1073744645 Mar 13 14:29 bin.000025 -rw-rw---- 1 mysql mysql 685764054 Mar 31 00:40 bin.000026 -rw-rw---- 1 mysql mysql 2474 Mar 31 00:40 bin.000028 -rw-rw---- 1 mysql mysql 648984 Mar 31 00:40 bin.000029 -rw-rw---- 1 mysql mysql 14068 Mar 31 00:40 bin.000027 -rw-rw---- 1 mysql mysql 541344128 Apr 12 12:04 bin.000030 -rw-rw---- 1 mysql mysql 182 Apr 12 12:05 log-bin.index -rw-rw---- 1 mysql root 18611060 Apr 12 12:05 error.log -rw-rw---- 1 mysql mysql 80508261 Apr 13 23:47 bin.000031 root@host /var/log/mysql # du -ks . 2346720 .
2.3 Gigs - yeah that's a lot and got some older logs in there - up to a month old..
mysql> show binary logs; +------------+------------+ | Log_name | File_size | +------------+------------+ | bin.000025 | 1073744645 | | bin.000026 | 685764054 | | bin.000027 | 14068 | | bin.000028 | 2474 | | bin.000029 | 648984 | | bin.000030 | 541344128 | | bin.000031 | 81014400 | +------------+------------+ 7 rows in set (0.00 sec)
mysql> purge binary logs to 'bin.000028'
-> ;
Query OK, 0 rows affected (6.45 sec)
mysql> show binary logs;
+------------+-----------+
| Log_name | File_size |
+------------+-----------+
| bin.000028 | 2474 |
| bin.000029 | 648984 |
| bin.000030 | 541344128 |
| bin.000031 | 81072551 |
+------------+-----------+
4 rows in set (0.00 sec)
mysql>
and the file system
root@host /var/log/mysql # ls -ltr total 627276 -rw-rw---- 1 mysql mysql 2474 Mar 31 00:40 bin.000028 -rw-rw---- 1 mysql mysql 648984 Mar 31 00:40 bin.000029 -rw-rw---- 1 mysql mysql 541344128 Apr 12 12:04 bin.000030 -rw-rw---- 1 mysql root 18611060 Apr 12 12:05 error.log -rw-rw---- 1 mysql mysql 81056831 Apr 14 00:04 bin.000031 -rw-rw---- 1 mysql mysql 104 Apr 14 00:04 log-bin.index
MySQL - lost root password on slave server the other day - mysql was over-writing root password on slave - which is different for security. Uncommented
replicate-wild-ignore-table = mysql.%
on slave /etc/my.cnf after reset of password and its been fine since.
Either way, needed to reset root:
root@host ~ # mysql -u root@localhost -p'password'
ERROR 1045 (28000): Access denied for user 'root@localhost'@'localhost' (using password: YES)
root@host ~ # service mysqld stop
Shutting down MySQL. SUCCESS!
root@host ~ # mysqld_safe --skip-grant-tables &
[1] 13845
root@host ~ # Starting mysqld daemon with databases from /var/lib/mysql
root@host ~ # mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.NN-community-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> UPDATE user SET password=PASSWORD("newpassword") WHERE user="root"; flush privileges;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
root@host ~ # mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.67-community-log MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>