MySQL主从复制
MySQL主从复制介绍
1.1.1 作用
1.数据安全(异机实时备份)
2.持续服务（宕机接管）
1.1.2 主从复制实现基本原理
1.自带功能。
2.通过把主库的binlog传送到从库，从新解析应用到从库。
1.1.3 复制架构
1.2 MySQL主从复制的企业应用场景
应用场景1：从服务器作为主服务器的实时数据备份
应用场景2：主从服务器实现读写分离，从服务器实现负载均衡
应用场景3：把多个从服务器根据业务重要性进行拆分访问
1.3 MySQL主从复制原理介绍
1、开启binlog日志，通过把主库的binlog传送到从库，从新解析应用到从库。
2、复制需要3个线程完成，5.6从库多个sql。
3、复制是异步的过程。
主从复制是异步的逻辑的SQL语句级的复制。
主从复制实践
1、主库开启binlog日志
/etc/my.cnf:
log_bin = /application/mysql/logs/mcw01-bin
server-id = 52
从库调整(从库不开binlog)
server-id = 53
各自重启。
/etc/init.d/mysqld restart
[root@mcw01 ~]$ grep -v "^#" /etc/my.cnf|egrep "server_id|log_bin" #主库 log_bin = /application/mysql/logs/mcw01-bin server_id = 52 [root@mcw01 ~]$ [root@mcw02 ~]$ grep -v "^#" /etc/my.cnf|egrep "server_id|log_bin" #从库 server_id = 53 [root@mcw02 ~]$ [root@mcw02 ~]$ /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@mcw02 ~]$ [root@mcw01 ~]$ /etc/init.d/mysqld start Starting MySQL. SUCCESS! [root@mcw01 ~]$ [root@mcw01 ~]$ mysql -uroot -p123456 -e "show variables like 'log_bin'" #主库查看 Warning: Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ [root@mcw01 ~]$ mysql -uroot -p123456 -e "show variables like 'server_id'" Warning: Using a password on the command line interface can be insecure. +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 52 | +---------------+-------+ [root@mcw01 ~]$
2、主库建立账号给从库用的
grant replication slave on *.* to rep@'172.16.0.%' identified by '123456';
[root@mcw01 ~]$ mysql -uroot -p123456 ..... mysql> grant replication slave on *.* to rep@'172.16.0.%' identified by '123456'; Query OK, 0 rows affected (0.23 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> select user,host from mysql.user; +--------+------------+ | user | host | +--------+------------+ | root | % | | root | 127.0.0.1 | | rep | 172.16.0.% | | rep | 172.16.1.% | | root | ::1 | | | localhost | | backup | localhost | | root | localhost | | | mcw01 | | root | mcw01 | +--------+------------+ 10 rows in set (0.07 sec) mysql> show grants for rep@'172.16.0.%'; +-------------------------------------------------------------------------------------------------------------------------+ | Grants for rep@172.16.0.% | +-------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'rep'@'172.16.0.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +-------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
3、主库锁表备份
对主库锁表只读，当前窗口不能关闭
flush table with read lock;
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mcw-bin.000017 | 405 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
重开窗口导出数据。
mysqldump -A -B |gzip>/opt/bak.sql.gz
第一窗口解锁。
unlock table;
scp -rp /opt/bak.sql.gz root@172.16.1.53:/opt
不停库不锁表：定时任务。
mysqldump -A -B --master-data=2 |gzip>/opt/bak.sql.gz
数据量大 物理打包。
mysql> flush table with read lock; Query OK, 0 rows affected (0.00 sec) mysql> show variables like "%timeout%"; +-----------------------------+----------+ | Variable_name | Value | +-----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_flush_log_at_timeout | 1 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | rpl_stop_slave_timeout | 31536000 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | +-----------------------------+----------+ 12 rows in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mcw01-bin.000010 | 611 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) [root@mcw01 ~]$ mysqldump -uroot -p123456 -A -B |gzip>/opt/bak.sql.gz Warning: Using a password on the command line interface can be insecure. [root@mcw01 ~]$ ls /opt/bak.sql.gz /opt/bak.sql.gz [root@mcw01 ~]$ mysql> unlock table; Query OK, 0 rows affected (0.00 sec) mysql> \q Bye [root@mcw01 ~]$ scp -rp /opt/bak.sql.gz root@172.16.0.12:/opt root@172.16.0.12's password: bak.sql.gz 100% 176KB 5.1MB/s 00:00 [root@mcw01 ~]$
4、从库恢复
gzip -d bak.sql.gz
mysql <bak.sql
[root@mcw02 ~]$ gzip -d /opt/bak.sql.gz [root@mcw02 ~]$ ls /opt/ bak.sql [root@mcw02 ~]$ mysql -uroot -p123456 </opt/bak.sql Warning: Using a password on the command line interface can be insecure. [root@mcw02 ~]$ mysql -uroot -p123456 -e 'select * from mcw.test;' Warning: Using a password on the command line interface can be insecure. +----+------------------+ | id | name | +----+------------------+ | 1 | mcw | | 2 | xiaoma | | 3 | inca | | 4 | zuma | | 5 | kaka | | 6 | mingming | | 7 | baibai | | 8 | mcw_inc1_1 | | 9 | mcw_inc1_2 | | 10 | mcw_zengliang2_1 | | 11 | mcw_zengliang2_2 | +----+------------------+ [root@mcw02 ~]$
5、从库 change master
文件和日志位置，是前面show master status 查看的，
CHANGE MASTER TO
MASTER_HOST='172.16.0.11',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mcw01-bin.000010',
MASTER_LOG_POS=611;
也可以不用进入mysql的命令行执行
[root@mcw02 ~]$ mysql -uroot -p123456 <<EOF > CHANGE MASTER TO > MASTER_HOST='172.16.0.11', > MASTER_PORT=3306, > MASTER_USER='rep', > MASTER_PASSWORD='123456', > MASTER_LOG_FILE='mcw01-bin.000010', > MASTER_LOG_POS=611; > EOF Warning: Using a password on the command line interface can be insecure. [root@mcw02 ~]$ [root@mcw02 ~]$ ls /application/mysql/data/ auto.cnf ib_logfile0 master.info mcw02.pid mcw02-relay-bin.index mysql relay-log.info ibdata1 ib_logfile1 mcw mcw02-relay-bin.000001 mcw_db2 performance_schema test [root@mcw02 ~]$ cat /application/mysql/data/master.info 上面命令的实际作用是将信息写入从库这个文件中 23 mcw01-bin.000010 611 172.16.0.11 rep #用户 123456 #密码等信息 3306 60 0 0 1800.000 0 86400 0 [root@mcw02 ~]$
6、启动从库同步开关并测试主从复制
mysql> start slave;
mysql> show slave status\G
[root@mcw02 ~]$ mysql -uroot -p123456 -e 'start slave;' Warning: Using a password on the command line interface can be insecure. [root@mcw02 ~]$ mysql -uroot -p123456 -e "show slave status\G"|egrep -i "IO_Running|SQL_Running:|Seconds_Behind_Master" Warning: Using a password on the command line interface can be insecure. Slave_IO_Running: Yes Slave_SQL_Running: Yes Seconds_Behind_Master: 0 [root@mcw02 ~]$ [root@mcw02 ~]$ mysql -uroot -p123456 -e "show slave status\G" Warning: Using a password on the command line interface can be insecure. *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.11 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mcw01-bin.000010 Read_Master_Log_Pos: 611 Relay_Log_File: mcw02-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mcw01-bin.000010 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: 611 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: 52 Master_UUID: 0598e4cb-9858-11ec-9444-000c294f4092 Master_Info_File: /application/mysql/data/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 [root@mcw02 ~]$ [root@mcw01 ~]$ mysql -uroot -p123456 -e "create database mcw_zhucong;" #主库创建 Warning: Using a password on the command line interface can be insecure. [root@mcw02 ~]$ mysql -uroot -p123456 -e "show databases;" #从库查询到数据库mcw_zhucong Warning: Using a password on the command line interface can be insecure. +--------------------+ | Database | +--------------------+ | information_schema | | mcw | | mcw_db2 | | mcw_zhucong | | mysql | | performance_schema | | test | +--------------------+ [root@mcw02 ~]$
查看主从线程
[root@mcw01 ~]$ mysql -uroot -p123456 -e "show processlist\G;"
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
Id: 7
User: rep
Host: 172.16.0.12:15729
db: NULL
Command: Binlog Dump
Time: 1210
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 11
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
[root@mcw01 ~]$
[root@mcw02 ~]$ mysql -uroot -p123456 -e "show processlist\G;"
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row *************************** #io线程
Id: 5
User: system user
Host:
db: NULL
Command: Connect
Time: 1261
State: Waiting for master to send event
Info: NULL
*************************** 2. row *************************** #sql线程
Id: 6
User: system user
Host:
db: NULL
Command: Connect
Time: 217
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
*************************** 3. row ***************************
Id: 14
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: init
Info: show processlist
[root@mcw02 ~]$
