挂的是一台监控系统的从库，起不来，怎么都起不来，没办法重建。
mysqldump --single-transaction --master-data monitor > monitor.sql
mysqldump: Got errno 28 on write
没想到空间满了：
du -sh mysqllogsa/
222G mysqllogsa/
mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 15 |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
要换成7 天啦：
mysql> set global expire_logs_days=7;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%expire_logs_days%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| expire_logs_days | 7 |
+------------------+-------+
1 row in set (0.00 sec)
mysql>
命令改完了记得也要改配置文件参数：my.cnf：
expire_logs_days = 7
执行删除的命令：
mysql> PURGE MASTER LOGS BEFORE '2013-01-24 15:00:00';
Query OK, 0 rows affected (1.09 sec)
瞬间变成了一半大小了：
[root@NANY181-209-SJHL-APP diska]# du -sh
107G .
再次执行dump 命令（南无阿弥陀佛）：
mysqldump --single-transaction --master-data monitor > monitor.sql
搞完之后，将文件复制到备库。
备库上的操作：
mkdir mysqldatasa
chown -R mysql:mysql mysqldatasa/
配置好my.cnf 文件
启动mysql：检查错误文件发现：
Fatal error: Can't open and lock privilege tables: Table 'mysql.host'
doesn't exist
没有mysql 表，那当然不行了，创建：
cd /home/percona-mysql-5.5.23/scripts
./mysql_install_db --datadir=/diskb/mysqldatasa/
--basedir=/home/percona-mysql-5.5.23/ --defaults-file=/diskb/mysqldatasa/my.cnf
后面发现起不来，
/bin/sh /home/percona-mysql-5.5.23/bin/mysqld_safe
--defaults-file=/diskb/mysqldatasa/my.cnf &
把旧的文件删除掉，重新来过，后面补充一个 --user 参数，再试，就可以了：
./mysql_install_db --datadir=/diskb/mysqldatasa/
--basedir=/home/percona-mysql-5.5.23/ --defaults-file=/diskb/mysqldatasa/my.cnf
--user=mysql
数据库起来后，进去创建一个database monitor；
然后就开始导入数据了：
[root@NANY181-206-SJHL-APP backup]# mysql -uroot
--socket=/dev/shm/mysql.sock monitor < monitor.sql
这个时间未知啊，先不管吧，晚上吃饭，要管肚子先
数据都dump 到数据库里面去了，现在开始设置从库了：
mysql> CHANGE MASTER TO MASTER_HOST='10.26.80.209',
MASTER_USER='dbbak', MASTER_PASSWORD='justdoit',
MASTER_LOG_FILE='mysql-bin.003360',
MASTER_LOG_POS=470705080,MASTER_PORT=49710;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
检查从库的状态：好像有报错哦，提示是表不存在：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.26.80.209
Master_User: dbbak
Master_Port: 49710
Connect_Retry: 60
Master_Log_File: mysql-bin.003361
Read_Master_Log_Pos: 94436927
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 2370253
Relay_Master_Log_File: mysql-bin.003360
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1146
Last_Error: Error 'Table
'monitor.app_alarm_data_20130201_03' doesn't exist' on query. Default database:
'monitor'. Query: 'replace into App_alarm_data_20130201_03 values(67112963,
2610160506, 157, '3081')'
Skip_Counter: 0
Exec_Master_Log_Pos: 473075080
Relay_Log_Space: 160203490
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1146
Last_SQL_Error: Error 'Table
'monitor.app_alarm_data_20130201_03' doesn't exist' on query. Default database:
'monitor'. Query: 'replace into App_alarm_data_20130201_03 values(67112963,
2610160506, 157, '3081')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 80209
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
根据错误提示，发现这个表不存在，在主库上show tables 看了一下，今天的日表都没有，一直报错来着，于是创建这个表咯。
另外有一个跳过此错误的设置，我这里由于错误过多，也不好skip 了
mysql> slave stop;
Query OK, 0 rows affected (0.22 sec)
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
slave stop; slave start; 了几次，终于ok 了
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.26.80.209
Master_User: dbbak
Master_Port: 49710
Connect_Retry: 60
Master_Log_File: mysql-bin.003362
Read_Master_Log_Pos: 278331406
Relay_Log_File: mysql-relay-bin.000037
Relay_Log_Pos: 111368755
Relay_Master_Log_File: mysql-bin.003362
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 278331406
Relay_Log_Space: 111369057
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 80209
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
没有评论:
发表评论