MySQL主从复制
mysql支持单向 双向 链式级联 实时 异步复制，在复制过程中，一台服务器充当主服务器（Master）,而一个或多个其他服务器充当从服务器(Slave)
mysql主从复制的应用场景
1、主从服务器互为备份
2、主从服务器读写分离分担网站压力
读写分离
中大型公司：通过程序(php,Java)
测试环境：代理软件(mysql-proxy,amoeba)
门户网站：分布式dbproxy（读写分离，hash负载均衡，健康检查）
主从同步实践操作（多实例环境）
1、主库上面设置server-id值并开启binlog参数
[root@CentOS03 ~]# egrep "log-bin|server-id" /data/3306/my.cnf
log-bin = /data/3306/mysql-bin
server-id = 1
检查实际配置效果
[root@centos03 ~]# mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show variables like 'log_bin';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
2、建立用于同步的账号
mysql> grant replication slave on *.* to rep@'172.16.80.%' identified by '123456';
说明：replication slave 是mysql同步的必须权限，此处不要授权all
mysql> flush privileges;
查看授权后的结果
mysql> show grants for rep@'172.16.80.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for rep@172.16.80.% |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rep'@'172.16.80.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3、锁表，导出数据库
mysql> flush table with read lock; #该窗口不能断，新开一个窗口做数据库导出操作
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 332 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
[root@centos03 ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -A -B --events --master-data=2 > /opt/rep.sql #导出所有数据库
[root@centos03 ~]# vim /opt/rep.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=332; #可以看到该语句的记录位置和上面show master status是一样的，注释状态
4、数据库导出后，解锁
mysql> show master status; #再次查看位置点，以验证上面的锁表操作是否有效
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 332 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
5、从库上面
[root@centos03 ~]# egrep "log-bin|server-id" /data/3307/my.cnf
#log-bin = /data/3307/mysql-bin #log-bin无需开启
server-id = 3 #server-id的值不能和主库上面的值一样
[root@centos03 ~]# mysql -uroot -p123456 -S /data/3307/mysql.sock < /opt/rep.sql #导入从主库备份的数据库
[root@centos03 ~]# mysql -uroot -phello123 -S /data/3307/mysql.sock
mysql> change master to \
-> master_host='172.16.80.118',\
-> master_user='rep',\
-> master_password='123456',\
-> master_log_file='mysql-bin.000002',\
-> master_log_pos=332;
Query OK, 0 rows affected (0.03 sec)
验证一下
[root@centos03 ~]# cat /data/3307/data/master.info
18
mysql-bin.000002
332
172.16.80.118
rep
123456
3306
60
0
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G; #观察Slave_IO和Slave_SQL 这两个线程的状态是否是yes
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.80.118
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 332
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 332
Relay_Log_Space: 403
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)
