mysql主从备份_mysql主从复制配置

最新推荐文章于 2023-04-08 15:22:00 发布

weixin_39564151

最新推荐文章于 2023-04-08 15:22:00 发布

阅读量76

点赞数

文章标签： mysql主从备份

本文链接：https://blog.csdn.net/weixin_39564151/article/details/113117848

版权

一、mysql主从复制的基本原理：

我们这个实验演示的是mysql的异步复制，何谓异步复制，即一条语句开始，主服务器不会等待从服务器执行完，而是主服务器一执行完就开始下一条语句，这也是出于主服务器并发型的考虑。slave端会开启两个thread，即Slave I/O thread和Slave SQL thread，I/O thread负责从master的二进制日志中读取事件并将这些时间信息存放发到relay-log中，Slave SQL thread负责从relay-log日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。

所以说：主服务器上必须开启二进制日志，而从服务器上只需开启中继日志。

二、mysql支持的复制类型：

1. 基于语句的复制：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制。

2. 基于行的复制：把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

3. 混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

三、步骤：

主mysql服务器的配置：

1. 开启二进制日志

2. 创建具有复制权限的用户

3. Server-id唯一

从mysql服务器的配置：

1. 开启中继日志

2. Server-id唯一

3. 设为只读模式

假设主服务器上的数据比较大，同步会比较慢，所以要先用mysqldump导出主服务器的数据，再导入到从服务器上，然后再开始同步，这样比较合理。

四、准备：

在主服务器上创建一张测试表mysql> create database data;

Query OK, 1 row affected (0.03 sec)

mysql> select * from data.info;

+-----+-------+-----+

| sid | name | age |

+-----+-------+-----+

| 1 | zhang | 23 |

| 2 | li | 42 |

| 3 | wang | 34 |

| 4 | qian | 25 |

| 5 | cheng | 29 |

+-----+-------+-----+

5 rows in set (0.00 sec)

五、开始配置

主服务器：

1. 开启二进制日志并指定server-id[root@oracle ~]# vim /etc/my.cnf

[client]

user=root

password=12345

[mysqld]

log-bin=mysql-bin

server-id=1

#添加这几行，mysql5.6默认没有开启二进制日志文件[root@oracle ~]# service mysqld restart

Shutting down MySQL.. [ OK ]

Starting MySQL. [ OK ]

2. 创建具有复制权限的用户mysql> grant replication slave on *.* to slave@192.168.2.96 identified by '12345';

Query OK, 0 rows affected (0.04 sec)

从服务器：

1. 开启中继日志并指定server-id和只读[root@node2 ~]# vim /etc/my.cnf

[client]

user=root

password=12345

[mysqld]

relay-log=mysql-relay-bin

server-id=2

read-only=on

[root@node2 ~]# service mysqld restart

Shutting down MySQL.... [确定]

Starting MySQL. [确定]

同步：

1. 在主务器上进行逻辑备份[root@oracle ~]# mysqldump --master-data=2 --databases data --lock-all-tables > /root/bak.sql

[root@oracle ~]# scp /root/bak.sql root@192.168.2.96:/root/

2. 在从服务器上执行该脚本[root@node2 ~]# mysql < /root/bak.sql

[root@node2 ~]# mysql -e 'select * from data.info;'

+-----+-------+-----+

| sid | name | age |

+-----+-------+-----+

| 1 | zhang | 23 |

| 2 | li | 42 |

| 3 | wang | 34 |

| 4 | qian | 25 |

| 5 | cheng | 29 |

+-----+-------+-----+

#主服务器上的data数据库都导入到从服务器了

3. 让从服务器成为slave[root@node2 ~]# vim /root/bak.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;

mysql> change master to master_host='192.168.2.93',master_user='slave',master_password='12345',MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=120;

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

查看日志：2014-02-23 20:45:47 17372 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='slave', master_port= 3306, master_log_file='mysql-bin.000004', master_log_pos= 120, master_bind=''. New state master_host='192.168.2.93', master_port= 3306, master_log_file='mysql-bin.000004', master_log_pos= 120, master_bind=''.

2014-02-23 20:45:52 17372 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2014-02-23 20:45:52 17372 [Note] Slave I/O thread: connected to master 'slave@192.168.2.93:3306',replication started in log 'mysql-bin.000004' at position 120

2014-02-23 20:45:52 17372 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

2014-02-23 20:45:52 17372 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000004' at position 120, relay log './mysql-relay-bin.000001' position: 4

#slave I/O thread和Slave SQL thread都已经启动mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.93

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 2058

Relay_Log_File: mysql-relay-bin.000005

Relay_Log_Pos: 555

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4. 在主服务器上再插入2行数据[root@oracle ~]# mysql -e 'select * from data.info;'

+-----+-------+-----+

| sid | name | age |

+-----+-------+-----+

| 1 | zhang | 23 |

| 2 | li | 42 |

| 3 | wang | 34 |

| 4 | qian | 25 |

| 5 | cheng | 29 |

| 6 | six | 33 |

| 7 | seven | 41 |

+-----+-------+-----+

#第6行和第7行是新插入的，本来是没有的

5. 在从服务器上查看[root@node2 ~]# mysql -e 'select * from data.info;'

+-----+-------+-----+

| sid | name | age |

+-----+-------+-----+

| 1 | zhang | 23 |

| 2 | li | 42 |

| 3 | wang | 34 |

| 4 | qian | 25 |

| 5 | cheng | 29 |

| 6 | six | 33 |

| 7 | seven | 41 |

+-----+-------+-----+

这样就实现了主从同步了。

六、指定要同步的数据库或者表[root@node2 ~]# vim /etc/my.cnf

[mysqld]

replicate-do-db=data[root@node2 ~]# service mysqld restart

Shutting down MySQL.. [确定]

Starting MySQL. [确定]mysql> show slave status\G'

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.93

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 2058

Relay_Log_File: mysql-relay-bin.000007

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: data

#这里可以看到mysql同步的时候，就只会同步data数据库了

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

注意：使用replicate_do_db和replicate_ignore_db参数时，跨库执行的sql语句是不会同步的，如：#主服务器

mysql> use mysql

mysql> update data.info set age=40 where sid=13;

这样从服务器是不会同步的。可以使用replicate_wild_do_table和replicate_wild_ignore_table来代替，如：replicate_wild_do_table=data.%

七、半同步复制

半同步的意思表示MASTER 只需要接收到其中一台SLAVE的返回信息，就会commit；否则需等待直至达到超时时间然后切换成异步再提交。这个做可以使主从库的数据的延迟较小，可以在损失很小的性能的前提下提高数据的安全性。

主服务器配置：mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';

Query OK, 0 rows affected (0.05 sec)

#加载插件

[root@oracle ~]# vim /etc/my.cnf

[mysqld]

rpl-semi-sync-master-enabled=on #开启半同步

rpl-semi-sync-master-timeout=1000 #超时时间为1000ms

[root@oracle ~]# service mysqld restart

Shutting down MySQL.. [ OK ]

Starting MySQL. [ OK ]

mysql> show global variables like '%rpl%';

+------------------------------------+----------+

| Variable_name | Value |

+------------------------------------+----------+

| rpl_semi_sync_master_enabled | ON |

| rpl_semi_sync_master_timeout | 1000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | ON |

| rpl_stop_slave_timeout | 31536000 |

+------------------------------------+----------+

5 rows in set (0.00 sec)

从服务器配置：mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

Query OK, 0 rows affected (0.00 sec)

#加载插件

[root@node2 ~]# vim /etc/my.cnf

rpl-semi-sync-slave-enabled=on

[root@node2 ~]# service mysqld restart

Shutting down MySQL.... [确定]

Starting MySQL. [确定]

mysql> show global variables like '%rpl%';

+---------------------------------+----------+

| Variable_name | Value |

+---------------------------------+----------+

| rpl_semi_sync_slave_enabled | ON |

| rpl_semi_sync_slave_trace_level | 32 |

| rpl_stop_slave_timeout | 31536000 |

+---------------------------------+----------+

3 rows in set (0.00 sec)

mysql> stop slave;

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.02 sec)

这样，半同步就完成了

看一下，主、从服务器的状态：mysql> show status like '%rpl%';

+--------------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------------+-------+

| Rpl_semi_sync_master_clients | 1 |

| Rpl_semi_sync_master_net_avg_wait_time | 0 |

| Rpl_semi_sync_master_net_wait_time | 0 |

| Rpl_semi_sync_master_net_waits | 0 |

| Rpl_semi_sync_master_no_times | 0 |

| Rpl_semi_sync_master_no_tx | 0 |

| Rpl_semi_sync_master_status | ON |

| Rpl_semi_sync_master_timefunc_failures | 0 |

| Rpl_semi_sync_master_tx_avg_wait_time | 0 |

| Rpl_semi_sync_master_tx_wait_time | 0 |

| Rpl_semi_sync_master_tx_waits | 0 |

| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |

| Rpl_semi_sync_master_wait_sessions | 0 |

| Rpl_semi_sync_master_yes_tx | 0 |

+--------------------------------------------+-------+

14 rows in set (0.03 sec)

#主服务器上半同步的客户端的数量已经变成1了mysql> show status like '%rpl%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Rpl_semi_sync_slave_status | ON |

+----------------------------+-------+

1 row in set (0.00 sec)

#从服务器上显示为on状态

weixin_39564151

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql主从备份_mysql主从复制配置

一、mysql主从复制的基本原理：我们这个实验演示的是mysql的异步复制，何谓异步复制，即一条语句开始，主服务器不会等待从服务器执行完，而是主服务器一执行完就开始下一条语句，这也是出于主服务器并发型的考虑。slave端会开启两个thread，即Slave I/O thread和Slave SQL thread，I/O thread负责从master的二进制日志中读取事件并将这些时间信息存放发到r...
复制链接

扫一扫

mysql主从备份_mysql主从复制配置

“相关推荐”对你有帮助么？