十七、Mysql的主从（二）--主从复制部署
99.9% ----> 0.001*365*24*60=525.6 min 99.99% ----> 0.0001*365*24*60=52.56 min 99.999% ----> 0.0001*365*24*60=5.256 min
随着访问量的不断增加，单台MySQL数据库服务器压力不断增加，需要对MYSQL进行优化和架构改 造，MYQSL优化如果不能明显改善压力情况，可以使用高可用、主从复制、读写分离来、拆分库、拆分 表来进行优化。 MYSQL主从复制集群在中小企业、大型企业中被广泛使用，MYSQL主从复制的目的是实现数据库冗余 备份，将Master数据库数据定时同步至Slave库中，一旦Master数据库宕机，可以将WEB应用数据库配 置快速切换至Slave数据库，确保WEB应用较高的可用性。
主要作用：实现备份； 实现故障转移； 实现读写分离。
实现的方式：
1、基于二进制日志复制的 2、主库的修改操作会记录二进制日志 3、从库会请求新的二进制日志并回放,最终达到主从数据同步 4、主从复制核心功能: 辅助备份,处理物理损坏 扩展新型的架构:高可用,高性能,分布式架构等
1、两台以上mysql实例 ,server_id,server_uuid不同 2、主库开启二进制日志 3、专用的复制用户 4、保证主从开启之前的某个时间点,从库数据是和主库一致。 5、告知从库,复制user,passwd,IP port,以及复制起点(change master to) 6、线程(三个):Dump thread IO thread SQL thread 开启(start slave)
四、主从复制搭建(Classic replication)
1、实验环境
IP:192.168.32.201 多实例3307、3308 3307为master 3308为slave mysql多实例部署省略请参照《Mysql的多实例部署》 系统：CentOS Linux release 7.6.1810 (Core) mysql：mysql-5.7.20-linux-glibc2.12-x86_64
[root@vm01 ~]# systemctl start mysqld3307 [root@vm01 ~]# systemctl start mysqld3308 [root@vm01 ~]# ss -antlp|grep 330 LISTEN 0 80 :::3307 :::* users:(("mysqld",pid=7201,fd=31)) LISTEN 0 80 :::3308 :::* users:(("mysqld",pid=13480,fd=31))
主库开启server_id和log_bin日志
[root@vm01 ~]# cat /data/3307/my.cnf [mysqld] basedir=/app/mysql datadir=/data/3307/data socket=/data/3307/mysql.sock log_error=/data/3307/mysql.log port=3307 server_id=3307 log_bin=/data/3307/mysql-bin [mysql] socket=/data/3307/mysql.sock
从库开启server_id和relay_lob [root@vm01 ~]# cat /data/3308/my.cnf [mysqld] basedir=/app/mysql datadir=/data/3308/data socket=/data/3308/mysql.sock log_error=/data/3308/mysql.log port=3308 server_id=3308
relay_log=/data/3308/relay-log
log_bin=/data/3308/mysql-bin [mysql] socket=/data/3308/mysql.sock [root@vm01 ~]#systemctl restart mysql3307 [root@vm01 ~]#systemctl restart mysql3308 [root@vm01 ~]# mysql -S /data/3307/mysql.sock -p -e "select @@server_id;" Enter password: +-------------+ | @@server_id | +-------------+ | 3307 | +-------------+ [root@vm01 ~]# mysql -S /data/3308/mysql.sock -p -e "select @@server_id;" Enter password: +-------------+ | @@server_id | +-------------+ | 3308 | +-------------+ [root@vm01 ~]# [root@vm01 ~]# mysql -S /data/3307/mysql.sock -p -e "select @@log_bin;" Enter password: +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+ [root@vm01 ~]# mysql -S /data/3308/mysql.sock -p -e "select @@log_bin;" Enter password: +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+
[root@vm01 ~]# mysql -S /data/3307/mysql.sock -p [(none)]>grant replication slave on *.* to repl@'192.168.32.%' identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec) [(none)]>select user,host from mysql.user; +---------------+-----------+ | user | host | +---------------+-----------+ | repl | 10.0.0.% | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+-----------+ 4 rows in set (0.00 sec)
mysqldump -S /data/3307/mysql.sock -A -E --master-data=2 --single-transaction -R --triggers >/backup/full3307.sql #二进制日志开始的点 [root@vm01 ~]# vim /backup/full3307.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444;
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p [(none)]>set sql_log_bin=0; [(none)]>source /backup/full3307.sql; [(none)]>set sql_log_bin=1;
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p [none]>help change master to [none]>CHANGE MASTER TO MASTER_HOST='192.168.32.201', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444, MASTER_CONNECT_RETRY=10;
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p [(none)]>start slave; Query OK, 0 rows affected (0.00 sec)
[root@vm01 ~]# mysql -S /data/3308/mysql.sock -p [(none)]>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.32.201 Master_User: repl Master_Port: 3307 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 738 Relay_Log_File: vm01-relay-bin.000002 Relay_Log_Pos: 614 Relay_Master_Log_File: mysql-bin.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: 738 Relay_Log_Space: 820 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: 3307 Master_UUID: 11269f05-3166-11eb-9c7d-000c29d16f12 Master_Info_File: /data/3308/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) #其中 Slave_IO_Running: Yes # Slave_SQL_Running: Yes表明主从建立完成
1)查看主从库的数据是否一致 3307主库 [root@vm01 ~]# mysql -S /data/3307/mysql.sock -p Enter password: [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) 3308从库 [root@vm01 ~]# mysql -S /data/3308/mysql.sock -p Enter password: [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) 2）3307主库创建数据库ywx [root@vm01 ~]# mysql -S /data/3307/mysql.sock -p Enter password: [(none)]>create database ywx charset=utf8; Query OK, 1 row affected (0.00 sec) [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | ywx | +--------------------+ 5 rows in set (0.00 sec) 3)查看3308从库是否同步 [root@vm01 ~]# mysql -S /data/3308/mysql.sock -p Enter password: [(none)]>show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | ywx | +--------------------+ 5 rows in set (0.00 sec) #3308从库也有ywx数据库，主从复制部署成功！！！
I have a dream so I study hard!!!
