linux下 MySQL5.7 搭建主从复制

MySQL5.7安装见： https://blog.csdn.net/u013792404/article/details/91491142

 

mysql官方文档： https://dev.mysql.com/doc/refman/5.7/en/

 

1、主从配置

修改/etc/my.cnf

[mysqld]
port=3306
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
user=mysql
max_connections=100
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#忽略大小写
lower_case_table_names=1
#设置编码
character-set-server=utf8
collation-server=utf8_general_ci

#IP绑定
#bind-address=192.168.8.100

#配置主从
server-id=1   #服务器id (主从必须不一样)
log-bin=mysql-bin   #打开日志(主机需要打开)
binlog-do-db=book  #要给从机同步的库
binlog-ignore-db=mysql   #不给从机同步的库(多个写多行)
binlog-ignore-db=information_schema
expire_logs_days=7    #自动清理 7 天前的log文件,可根据需要修改

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#客户端连接设置
[client]
# 和上面要相同才行
socket=/usr/local/mysql/mysql.sock

default-character-set=utf8

 

2、重启服务

service   msyqld restart

 

3、测试bog_bin是否成功开启

mysql> show variables like '%log_bin%';

 

4、master的数据库中建立备份账号：backup为用户名，%表示任何远程地址，如下表示密码为'backup'的任何远程地址的backup都可以连接master主机

mysql> grant replication slave on *.* to 'backup'@'%' identified by 'backup' ;

mysql> use mysql ;

mysql> select user,authentication_string,host from user ;

 

5、主从2台数据库都创建book数据库

create database book;

 

6、查看主机状态： 其中file名称和position 在从数据库中会用到

mysql> show master status \G

 

 

7、修改从数据库的my.cnf

在其中添加：

#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错。

server_id=2
read_only=1
master_info_repository=TABLE
relay_log_info_repository=TABLE

#relay_log_recovery=1 #从机禁止写

#super_read_only=1 #从机禁止写

 

[mysqld]
port=3306
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/mysql.sock
user=mysql
max_connections=100
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#忽略大小写
lower_case_table_names=1
#设置编码
character-set-server=utf8
collation-server=utf8_general_ci

#IP绑定
#bind-address=192.168.8.100
#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错。
server_id=2
read_only=1
master_info_repository=TABLE
relay_log_info_repository=TABLE
#relay_log_recovery=1 #从机禁止写
#super_read_only=1 #从机禁止写

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#客户端连接设置
[client]
socket=/usr/local/mysql/mysql.sock
default-character-set=utf8

 

8、重启从数据库，并在其中配置主数据库信息。

service mysqld restart

登陆从数据库后的操作：

mysql> stop slave ;

mysql> change master to 
    -> master_host='192.168.8.104',      # 主数据库IP
    -> master_user='backup',                 #上面在主数据库中配置用于主从同步的用户名和密码
    -> master_password='backup',        #  密码
    -> master_log_file='mysql-bin.000005',    #  从主机状态查看到的  日志文件名称
    -> master_log_pos=154;                          #  日志文件偏移量 ， 和上面查看到的需要相同

mysql> start slave ;
 

 

 

9、查看从数据库状态

mysql> show slave status \G

 当Slave_IO_Running: Yes              Slave_SQL_Running: Yes 都为yes同步成功

 

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.8.104
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 154
               Relay_Log_File: CentOS-05-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             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: 531
              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: 1
                  Master_UUID: 623e966a-8d1e-11e9-ac4d-000c29f0282b
             Master_Info_File: mysql.slave_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)

mysql> 

 

 

10、 有时候需要 ，备份主库中的所有数据到从库中，保持当前主库和从库数据一致

备份前操作： 

通过使用命令行客户端连接到主服务器来启动主服务器上的会话，并通过执行以下FLUSH TABLES WITH READ LOCK语句来刷新所有表和阻止写语句：

mysql> FLUSH TABLES WITH READ LOCK;

警告

让发出FLUSH TABLES语句的客户端保持 运行状态，以使读锁定保持有效。如果退出客户端，则会释放锁定。

在master上的不同会话中，使用该 SHOW MASTER STATUS语句确定当前二进制日志文件的名称和位置：

 

#主库导出数据

mysqldump -uroot -proot  >/root/backup.sql

将backup.sql复制到从数据库 

 

#从库导入来自主库数据

source  /root/backup.sql

在从数据库中进行相关 住数据库的配置（见步骤8）

解除从数据库表锁定

unlock tables ;

 

 

11、测试同步，在主数据库插入表和数据

主数据库插入表，从数据库马上就有了数据表；主数据库插入数据，从数据库也有了数据； 同步测试成功。 

 

当从数据库停止后，主数据库进行了更新等操作； 需要同步数据到从数据库，然后配置主从（配置日志文件名和偏移位置）； 如果直接启动从数据库配置主从可能会报错。 查看show  slave status \G  会看到报错信息。

1、查看主从数据库，都没有表

2、在主数据库中建表

CREATE TABLE `book`.`book1`(  
  `id` INT NOT NULL,
  `name` VARCHAR(20),
  PRIMARY KEY (`id`)
);

查看数据库中表：  发现从数据库中也有数据表了，主从同步成功。 

 

测试插入数据：从数据库中有数据， 主从同步成功；

mysql> insert into book1 (id,name) values (1001,'JAVA从入门到精通') ;

 

12、其他命令

#查看错误
show warnning;
#查看主库状态
show master status \G
#查看从库状态
show slave status \G
#重置主记录信息
reset master;
#重置从记录信息
reset slave;
#停止始从
stop slave;
#开始从
start slave;
#清空从所有连接、信息记录
reset slave all;
#删除从
change master to master_host=' ';





#从库
stop slave;
reset slave all;
show slave status \G
#清除从库配置文件的配置
 


#主库
reset master;
#清除主库配置文件的配置
#清除mysql.user从库账号
show master status \G