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 

[root@mcw02 ~]$ gzip -d /opt/bak.sql.gz 
[root@mcw02 ~]$ ls /opt/
bak.sql
[root@mcw02 ~]$ mysql -uroot -p123456 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 ~]$

mysql

相关

关于MySQL中的事务、提交（commit）、回滚（rollback）

MySQL索引类型

Windows7环境，Dbeaver 安装连接Mysql5.7

linux 恢复mysql 误删除数据

mac 安装mysql workbench8.0.23闪退。

Linux-Mysql 遗忘密码如何解决？

mysql数据库死锁的产生原因及解决办法

linux安装mysqlclient库

【Mysql学习】django.db.utils.OperationalError: (1045:Access denied for user 'roo

mysql改变配置文件后启动报错

mysql binlog恢复命令

MySQL left join、right join和join的区别

标签