17.1 MySQL主从介绍
MySQL主从又叫做Replication、AB复制。简单讲就是A和B两台机器做主从后，在A上写数据，另外一台B也会跟着写数据，两者数据实时同步的。
MySQL主从是基于binlog的，主上须开启binlog才能进行主从。
主从过程大致有3个步骤
1）主将更改操作记录到binlog里
2）从将主的binlog事件(sql语句)同步到从本机上并记录在relaylog里
3）从根据relaylog里面的sql语句按顺序执行
主上有一个log dump线程，用来和从的I/O线程传递binlog。从上有两个线程，其中I/O线程用来同步主的binlog并生成relaylog，另外一个SQL线程用来把relaylog里面的sql语句落地。
MySQL主从原理图：
应用场景：
1、数据的备份。
2、备份，web应用在Slave上读数据，提高访问速度。
17.2 准备工作
主从设备安装mysql服务。
步骤：
1、wget下载安装包。
2、解压、mv到/usr/local/mysql。
3、初始化、编译、安装。
4、更改my.cnf和编辑启动脚本。
5、启动mysql服务。
17.3 配置主
修改my.cnf，增加server-id=200，log_bin=liang1
重启mysql服务，可以看到/data/mysql目录中生成了以liang1开头的文件，这时完成mysql主从的关键。
创建liangtest测试数据库。
[root@liang-00 ~]# mysqldump -uroot -p123456 mysql > /tmp/mysq.sql Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]# mysql -uroot -p123456 -e "create database liangtest;" Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]# mysql -uroot -p123456 liangtest < /tmp/mysq.sql Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]#
创建用户，用来主从相互同步用。
mysql> grant replication slave on *.* to 'repl'@192.168.37.203 identified by '123456'; Query OK, 0 rows affected (0.03 sec)
锁表，停止数据库写入数据。
mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)
查看master状态。
show master status; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | liang1.000001 | 1845 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
备份其它数据库。
[root@liang-00 ~]# mysqldump -uroot -p123456 zrlog > /tmp/zrlog.sql Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]# mysqldump -uroot -p123456 my_test_db > /tmp/my_test_db.sql Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]# mysqldump -uroot -p123456 db1 > /tmp/db1.sql Warning: Using a password on the command line interface can be insecure. [root@liang-00 ~]# ls /tmp/*.sql /tmp/db1.sql /tmp/my_test_db.sql /tmp/test.sql /tmp/zrlog.sql [root@liang-00 ~]#
17.4 配置从
修改my.cnf，增加server-id=203，要求和master不一样。
重启mysql服务。
拷贝master中的备份文件到slave。
scp 192.168.37.200:/tmp/*.sql /tmp/
[root@liang-03 ~]# scp 192.168.37.200:/tmp/*.sql /tmp/ root@192.168.37.200's password: db1.sql 100% 1774 885.6KB/s 00:00 my_test_db.sql 100% 1779 830.5KB/s 00:00 test.sql 100% 2324 245.2KB/s 00:00 zrlog.sql 100% 9988 613.7KB/s 00:00 [root@liang-03 ~]#
在slave中创建相应的数据库。
mysql> create database db1; Query OK, 1 row affected (0.05 sec) mysql> create database liangtest; Query OK, 1 row affected (0.00 sec) mysql> create database my_test_db; Query OK, 1 row affected (0.00 sec) mysql> create database zrlog; Query OK, 1 row affected (0.11 sec) mysql>
恢复数据。
mysql: [Warning] Using a password on the command line interface can be insecure. [root@liang-03 ~]# mysql -uroot -p123456 liangtest < /tmp/test.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@liang-03 ~]# mysql -uroot -p123456 my_test_db < /tmp/my_test_db.sql mysql: [Warning] Using a password on the command line interface can be insecure. [root@liang-03 ~]# mysql -uroot -p123456 zrlog < /tmp/zrlog.sql mysql: [Warning] Using a password on the command line interface can be insecure.
登录数据库。进行操作。
1、stop slave;
2、change master to master_host='192.168.37.200', master_user='repl', master_password='123456', master_log_file='liang1.000001', master_log_pos=1845;
mysql> stop slave; Query OK, 0 rows affected, 1 warning (0.08 sec) mysql> change master to master_host='', master_user='repl', master_password='', master_log_file='', master_log_pos=xx, -> ^C mysql> change master to master_host='192.168.37.200', master_user='repl', master_password='123456', master_log_file='liang1.000001', master_log_pos=1845; Query OK, 0 rows affected, 2 warnings (0.12 sec) mysql>
其中：
master_log_file='liang1.000001',master_log_pos=1845; 为master中 show master status;显示的数据。
3、start slave;
4、在master中：ulock tables;
mysql> start slave; Query OK, 0 rows affected (0.04 sec) mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
查看主从同步是否正常，在slave上：
show slave status\G
需关注：Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0 主延迟时间。
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.37.200 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: liang1.000001 Read_Master_Log_Pos: 1025 Relay_Log_File: liang-03-relay-bin.000002 Relay_Log_Pos: 280 Relay_Master_Log_File: liang1.000001 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: 1025 Relay_Log_Space: 456 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: 200 Master_UUID: 5b31c695-044c-11e9-bee2-000c29f58a7f Master_Info_File: /data/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec) mysql>
17.5 测试主从同步
在master和slave的my.cnf中可以自定义同步的内容：
可以在主服务器上
binlog-do-db= //仅同步指定的库
binlog-ignore-db= //忽略指定库
也可以在从服务器上
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
在线上生产中主要用：replicate_wild_do_table= //如aming.%, 支持通配符%
replicate_wild_ignore_table=
测试主从：
master中在db.t1中添加数据。
主：
mysql> insert into t1 values(1, 'abc'); Query OK, 1 row affected (20.81 sec) mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec) mysql>
从：
mysql> select * from t1; +------+------+ | id | name | +------+------+ | 1 | abc | +------+------+ 1 row in set (0.00 sec) mysql>
扩展：
不停库不锁表在线主从配置
http://seanlook.com/2015/12/14/mysql-replicas/
主从不同步
http://www.rfyy.net/archives/2309.html
http://blog.51cto.com/storysky/259280
主主
关于 auto_increment
https://blog.csdn.net/leshami/article/details/39779509
http://www.cnblogs.com/ygqygq2/p/6045279.html
mysql-proxy 实现读写分离
http://blog.51cto.com/zzclinux/1980487
mysql-proxy类似的产品有：
mycat 基于阿里的开源软件cobar，官网 www.mycat.io
https://my.oschina.net/ruoli/blog/1789370
mycat实现分库分表
https://www.cnblogs.com/joylee/p/7513038.html
atlas 出自于360，不维护不更新了
https://blog.csdn.net/AnPHPer/article/details/80566385
mysql环形主从
http://ask.apelearn.com/question/11437
mysql架构演变
http://www.aminglinux.com/bbs/thread-8025-1-1.html
MHA架构
http://blog.51cto.com/xiaoshuaigege/2060768
比较复杂的mysql集群架构
http://ask.apelearn.com/question/17026
323
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
