mysql主从同步

mysql主从同步:

——实现数据自动同步的服务结构

——主服务器: 接受客户端的访问连接

——从服务器: 自动同步主服务器数据

主从同步原理:

master: 启用binlog日志

slave: (1)、slave_IO : 复制master 主机binlog日志文件里的SQL命令到本机的elay-log文件里

(2)、slave_SQL: 执行本机relay-log 文件里的SQL语句,实现与master 数据一致

思路:

配置主服务器:启用binlog日志,授权用户,查看binlog日志信息

配置从服务器:设置server_id,确保与主服务器数据一致,指定主库信息,启动slave程序,查看状态信息

方案:

使用2台虚拟机,如图-1所示。其中192.168.4.51是主服务器,另一台192.168.4.52作为从服务器,通过调取主服务器上的binlog日志,在本地重做对应的库、表,实现与主服务器的数据同步。

环境: 主服务器、从服务器都已安装好mysql4.7

mysql主从同步

步骤一:配置主服务器192.168.4.51

1)启用binlog日志

  1. [root@master ~]vim /etc/my.cnf
  2. [mysqld]
  3. server_id=51            #server_id
  4. log-bin=master51         #日志名,自定义
  5. :wq
  6. systemctl restart mysqld

2)用户授权

用户名自定义、客户端地址使用% 或 只指定 从服务器的地址 都可以、只给复制数据的权限即可。

  1. [root@master ~]#mysql -uroot -p123456
  1. mysql> grant replication slave on *.* to repluser@”%” identified by “123456”;
  2. mysql>quit;

3)查看binlog日志信息

查看日志文件名 和 偏移量位置。

mysql> show master status \G;

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

File: master51.000001 #日志名

Position: 441 #偏移量

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

步骤二:配置主服务器192.168.4.51

1)指定server_id

Server_id值可以自定义,但不可以与主服务器相同。

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

[mysqld]

  1. server_id=52 #server_id值
  2. :wq
  3. [root@slave ~]# systemctl restart mysqld #重启服务

2)确保与主服务器数据一致(如果是使用2台新部署的数据库服务器配置主从同步,此操作可以忽略)

  1. [root@master ~]# mysqldump -uroot –p123456 –master-data -A > /all.sql

#在主服务器上备份数据

[root@master ~]# scp /all.sql root@192.168.4.52:/root/ #将备份文件拷贝给从服务器

[root@slave ~]#mysql -uroot –p123456 < /root/all.sql #从服务器使用备份文件恢复数据

如果备份的不是所有数据库,备份单个库,多个库;就需要在从服务器上先创建数据库再恢复数据库

mysql> create database 数据库名1,数据库名2 ; #在从服务器上创建与主服务器同名的数据库

grep “master51” /root/all.sql

#在从服务器查看备份文件中的binlog日志信息

CHANGE MASTER TO MASTER_LOG_FILE=’master51.000001′, MASTER_LOG_POS=441; #日志名与偏移量

3)指定主服务器信息

数据库管理员root本机登录,指定主服务器信息,其中日志文件名和偏移量 写allbak.sql文件记录的。

  1. [root@slave ~]# mysql -uroot –p123456
  2. mysql> change master to #指定主服务器
  3. -> master_host=“192.168.4.51”, #主服务器ip地址
  4. -> master_user=“repluser”, #主服务器授权用户
  5. -> master_password=“123qqq…A”, #主服务器授权用户密码
  6. -> master_log_file=“master51-bin.000001”, #主服务器日志文件
  7. -> master_log_pos=441;          #主服务器日志偏移量

4)查看从服务器数据库状态:

mysql> show slave status \G; //查看状态信息

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.4.51 #主服务器ip地址

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master51.000001

Read_Master_Log_Pos: 437

Relay_Log_File: host52relay-bin.000002

Relay_Log_Pos: 604

Relay_Master_Log_File: master51.000001

Slave_IO_Running: Yes        #IO线程yes状态

Slave_SQL_Running: Yes        #SQL线程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: 437

Relay_Log_Space: 812

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: 50

Master_UUID: 4881ee4b-8800-11e9-830a-525400001e32

Master_Info_File: /var/lib/mysql/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)

步骤三:客户端测试配置

1)在主服务器添加访问数据的连接用户

授权用户对所有数据有增删改查的权限即可

  1. [root@master ~]# mysql –uroot –p123456
  1. mysql> grant select,insert,update,delete on *.* to admin@”%” identified by “123456”;
  2. Query OK, 0 rows affected, 1 warning (0.03 sec)
  3. mysql> quit

2)客户端连接主服务器访问数据

在客户机, 使用主服务器的授权用户连接

  1. [root@client ~]# mysql -h192.168.4.51-uadmin -p123456
  1. mysql> show grants;
  2. +————————————————————+
  3. | Grants for admin@% |
  4. +————————————————————+
  5. | GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO ‘admin’@’%’ |
  6. +————————————————————+
  7. 1 row in set (0.00 sec)
  8. mysql> insert into db3.user(name,uid) values(“lili”,288); //db3库和user表是主从同步之前主服务器已有的。
  9. Query OK, 1 row affected (0.05 sec)
  10. mysql> insert into db3.user(name,uid) values(“lili”,288);
  11. Query OK, 1 row affected (0.28 sec)
  12. mysql> insert into db3.user(name,uid) values(“lili”,288);
  13. Query OK, 1 row affected (0.05 sec)
  14. mysql> select name,uid from db3.user where name=”lili”;
  15. +——+——+
  16. | name | uid |
  17. +——+——+
  18. | lili | 288 |
  19. | lili | 288 |
  20. | lili | 288 |
  21. +——+——+
  22. 3 rows in set (0.00 sec)

3) 客户端连接从服务器访问数据

客户端主机使用授权用户连接从服务器可以看到和主服务器同样的数据

  1. [root@client ~]# mysql -h192.168.4.52 –uadmin -p123456
  1. mysql> select name,uid from db3.user where name=”lili”;
  2. +——+——+
  3. | name | uid |
  4. +——+——+
  5. | lili | 288 |
  6. | lili | 288 |
  7. | lili | 288 |
  8. +——+——+
  9. 3 rows in set (0.00 sec)

!!!!!!注意的是 从服务器的数据可以比主服务器多,不能少。

另外在搭建主从数据库开始的时候,就要保持数据一致,不然会导致从服务器执行relay-log 文件里的SQL语句报错。

本文版权归 飞翔沫沫情 作者所有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出 原文链接 如有问题, 可发送邮件咨询,转贴请注明出处:https://www.fxkjnj.com/407/

发表评论

登录后才能评论