mysqldump -uroot -A -B -F --master-data=2 --events | gzip > /tmp/mysql_bak.$(date +%F).sql.gz
##################
[root@MYSQL_S ~]# mysqldump -uroot -A -B -F --events --master-data=2 | gzip > /tmp/mysql_bak.$(date +%F).sql.gz
[root@MYSQL_S ~]# ll /tmp/
total 148
drwxr-xr-x 4 mysql mysql 4096 Nov 5 09:43 data
-rw-r--r-- 1 root root 145589 Nov 5 11:05 mysql_bak.2015-11-05.sql.gz
[root@MYSQL_S ~]#
给主数据解锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
或者：
mysql -uroot -e " unlock tables;"
###############################################
继续往主数据写数据
for n in `seq 100 150`;do mysql -uroot -e "use lvnian;insert test values($n,'lvnian$n'); " ;done;
mysql -uroot -e "use lvnian; select * from test;"
###############################################
启动备数据也就是从库（10.0.0.5）
/etc/init.d/mysqld restart
[root@MYSQL_C ~]# /etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@MYSQL_C ~]#
##########################################
把备份数据备份到备数据
scp root@10.0.0.4:/tmp/mysql_bak* .
ls
cp mysql_bak.2015-11-05.sql.gz /tmp/
gzip -d mysql_bak.2015-11-05.sql.gz
mysql -uroot < mysql_bak.2015-11-05.sql
mysql -uroot -e "show databases;"
mysql -uroot -e "use lvnian;select * from test;"
=========================================
[root@MYSQL_C ~]# scp root@10.0.0.4:/tmp/mysql_bak* .
root@10.0.0.4's password:
mysql_bak.2015-11-05.sql.gz 100% 142KB 142.2KB/s 00:00
[root@MYSQL_C ~]# ls
anaconda-ks.cfg chu.sh gao.txt hosts install.log install.log.syslog mysql_bak.2015-11-05.sql.gz
[root@MYSQL_C ~]#
##########
[root@MYSQL_C ~]# gzip -d mysql_bak.2015-11-05.sql.gz
[root@MYSQL_C ~]# mysql -uroot < mysql_bak.2015-11-05.sql
[root@MYSQL_C ~]# mysql -uroot -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| lvnian |
| mysql |
| test |
+--------------------+
[root@MYSQL_C ~]# mysql -uroot -e "use lvnian;select * from test;"
+-----+-----------+
| id | name |
+-----+-----------+
| 1 | lvnian1 |
| 2 | lvnian2 |
| 3 | lvnian3 |
| 4 | lvnian4 |
....
....
....
| 95 | lvnian95 |
| 96 | lvnian96 |
| 97 | lvnian97 |
| 98 | lvnian98 |
| 99 | lvnian99 |
| 100 | lvnian100 |
+-----+-----------+
[root@MYSQL_C ~]#
全部恢复到备数据成功
开始设置主从同步
修改从库的server-id，修改和主库不一样，这里修改为20
egrep "server-id" /etc/my.cnf
sed -i 's/^server-id/#server-id/g' /etc/my.cnf
egrep "server-id" /etc/my.cnf
sed -i '/#server-id/aserver-id = 20' /etc/my.cnf
egrep "server-id" /etc/my.cnf
###重启mysql
更改主数据刚才的文件名称和位置点，等到下面的内容
mysql
======
CHANGE MASTER TO
MASTER_HOST='10.0.0.103',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='lvnian123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=10447;
######################
start slave;
show slave status\G;
######################
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.103', <===注意，必须要用VIP
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='lvnian123456',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=302;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.4
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 9759
Relay_Log_File: S_MYSQL-relay-bin.000005
Relay_Log_Pos: 9905
Relay_Master_Log_File: mysql-bin.000004
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: 9759
Relay_Log_Space: 10106
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: 1
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
成功的表现：
[root@S_MYSQL ~]# mysql -uroot -e "show slave status\G;"|egrep "Slave_IO_Runnin|Slave_SQL_Running"
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@S_MYSQL ~]#
出现上面两个yes证明数据库主从同步成功了
再次用heartbeat让mysql主库进行主备切换。看看mysql客户端，也就是从库的状态。