MySQL主从复制【Linux】

阅读目录

• 一、环境准备
• 二、进行配置 
  • 1、修改主服务器master:
  • 2、修改从服务器slave:
  • 3、重启两台服务器的mysql
  • 4、在主服务器上建立帐户并授权slave:
  • 5、登录主服务器的mysql，查询master的状态
  • 6、配置从服务器Slave：
  • 7、检查从服务器复制功能状态：
  • 8、主从服务器测试：
• 三、取消主从复制

 

---

回到顶部

一、环境准备

系统版本：centos 7

1、服务器：

　　server1:182.92.209.212  master

　　server1:182.92.97.73 salve

2、myslq版本：

　　 mysql -V  命令查看MySQL数据库的版本，两台服务器版本一致，不知版本不一致是否有问题。

3、连接

　　查看是否能ping通另外一台服务器

4、关闭防火墙

?

1 2 3 4 5 6 7 8 9 10 11 12

1:查看防火状态     systemctl status firewalld     service  iptables status 2:暂时关闭防火墙     systemctl stop firewalld     service  iptables stop 3:永久关闭防火墙     systemctl disable firewalld     chkconfig iptables off 4:重启防火墙     systemctl enable firewalld     service iptables restart

回到顶部

二、进行配置 

1、修改主服务器master:

  #vi /etc/my.cnf 

?

1 2 3

[mysqld] log-bin=mysql-bin   //[必须]启用二进制日志 server-id=212      //[必须]服务器唯一ID，默认是1，一般取IP最后一段

【查看日志是否开启】

?

1	mysql> show global variables like "%log%";

log_bin                                    | ON 

【查看server相关信息】

?

1	mysql> show global variables like "%server%";

?

1 2 3 4 5 6 7 8 9 10 11

+---------------------------------+--------------------------------------+ | Variable_name                   | Value                                | +---------------------------------+--------------------------------------+ | character_set_server            | utf8mb4                              | | collation_server                | utf8mb4_general_ci                   | | innodb_ft_server_stopword_table |                                      | | server_id                       | 202                                  | | server_id_bits                  | 32                                   | | server_uuid                     | eb116683-ed0d-11eb-8a01-00163e361c72 | +---------------------------------+--------------------------------------+ 6 rows in set (0.00 sec)

2、修改从服务器slave:

  #vi /etc/my.cnf 

?

1 2 3 4

[mysqld] log-bin=mysql-bin   //[不是必须]启用二进制日志 server-id=73      //[必须]服务器唯一ID，默认是1，一般取IP最后一段 read-only=1       // [不是必须]，从库开启只读模式

3、重启两台服务器的mysql

?

1	/etc/init.d/mysqld restart

4、在主服务器上建立帐户并授权slave:

百分号表示所有客户端都可能连，

?

1	mysql>GRANT REPLICATION SLAVE ON *.* to '用户名'@'%' identified by '密码';

如果是mysql8之后需要先创建用户，之后才能授权：

?

1 2 3

CREATE USER '用户名'@'%' IDENTIFIED BY '密码'; grant replication slave on *.* to 用户; FLUSH PRIVILEGES;

查看用户的权限(包含REPLICATION才可以)

?

1 2 3 4 5 6 7

mysql> show grants for 'zhixi'@'%'; +-----------------------------------------------+ | Grants for zhixi@%                            | +-----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `zhixi`@`%` | +-----------------------------------------------+ 1 row in set (0.00 sec)

5、登录主服务器的mysql，查询master的状态

?

1 2 3 4 5 6 7

mysql> show master status;　 +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000015 |      154 |              |                  |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

注意：到这一步先不要动主机MYSQL，防止主服务器状态值变化

6、配置从服务器Slave：

?

1	mysql>change master to master_host='182.92.209.212',master_user='刚刚创建的用户名',master_password='密码',master_log_file='mysql-bin.000015',master_log_pos=154;

如果这一步出错了，去到最后先取消主从复制，再接着继续操作！

 //启动从服务器复制功能:

?

1	mysql> start slave;

7、检查从服务器复制功能状态：

?

1	mysql> show slave status\G

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

*************************** 1. row ***************************                Slave_IO_State: Waiting for master to send event                   Master_Host: 182.92.209.212                   Master_User: zhixi                   Master_Port: 3306                 Connect_Retry: 60               Master_Log_File: mysql-bin.000015           Read_Master_Log_Pos: 154                Relay_Log_File: zhizuo-relay-bin.000002                 Relay_Log_Pos: 320         Relay_Master_Log_File: mysql-bin.000015              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: 154               Relay_Log_Space: 528               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: 202                   Master_UUID: eb116683-ed0d-11eb-8a01-00163e361c72              Master_Info_File: /www/server/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及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。　　

以上主从复制搭建完成！

8、主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

mysql> create database db01; Query OK, 1 row affected (0.00 sec)   mysql> use db01; Database changed mysql> create table db_tb(id int(3),name char(10)); Query OK, 0 rows affected (0.02 sec)   mysql> insert into db_tb values(001,'bobu'); Query OK, 1 row affected (0.01 sec)   mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | bjpan              | | daishua_zhangzhi   | | db01               | | mybd               | | mysql              | | oss_zhangzhixi     | | performance_schema | | sys                | | wordpress          | +--------------------+ 10 rows in set (0.00 sec)

从机查看主机插入的数据是否过来：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | db01               | | hi_db              | | hi_db1             | | hi_db2             | | mysql              | | performance_schema | | sys                | +--------------------+ 8 rows in set (0.00 sec)   mysql> use db01; Database changed mysql> show tables; +----------------+ | Tables_in_db01 | +----------------+ | db_tb          | +----------------+ 1 row in set (0.00 sec)   mysql> select * from db_tb; +------+------+ | id   | name | +------+------+ |    1 | bobu | +------+------+ 1 row in set (0.00 sec)

成功！　　

回到顶部

三、取消主从复制