Linux（ubuntu）mysql数据库主从同步、配置

深藏阁楼爱情的钟 2022-05-28 01:49 219阅读 0赞

一、查看两台主机的版本Ubuntu 16.04.3

root@iZ2zeht3zvxbq5ycy698pwZ:~# lsb_release -a
    LSB Version:    core-9.20160110ubuntu0.2-amd64:core-9.20160110ubuntu0.2-noarch:security-9.20160110ubuntu0.2-amd64:security-9.20160110ubuntu0.2-noarch
    Distributor ID: Ubuntu
    Description:    Ubuntu 16.04.3 LTS
    Release:    16.04
    Codename:   xenial
    root@iZ2zeht3zvxbq5ycy698pwZ:~#

二、查看mysql的版本

mysql> select version(); +-------------------------+
    | version() | +-------------------------+
    | 5.7.21-0ubuntu0.16.04.1 | +-------------------------+
    1 row in set (0.00 sec)
    
    mysql>

三、以39.107.227.105当主机，39.107.116.135当从机  
先登录主机 A，在主服务器上，设置一个从数据库的账户，使用REPLICATION SLAVE（从复制）赋予权限，如：

mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'39.107.116.135' IDENTIFIED BY '123456';
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    
    mysql>

刷新权限

mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    mysql>

**如果有多台主机，就需要执行多次**

四、（这个方法不要用，请看第五步）(坑，这是个坑，mysql5.7版本的改了my.cnf根本就无法使用)打开主机A的my.cnf，输入如下：（修改主数据库的配置文件my.cnf，开启BINLOG，并设置server-id的值，修改之后必须重启mysql服务），位于/etc/mysql下的my.cnf

server-id               = 1    #主机标示，整数
    log_bin                 = /var/log/mysql/mysql-bin.log   #确保此文件可写，开启bin-log
    read-only              =0  #主机，读写都可以
    binlog-do-db         =test   #指定mysql的binlog日志记录哪个db,多个写多行
    binlog-ignore-db    =mysql #不需要备份的数据库，多个写多行

注意：如果要让mysql监听到其他的地址，可以将bind-address = 127.0.0.1注释掉。 
    或者将bind-address = 0.0.0.0监听所有的地址 
    1. 如果不按照如上所述设置，会报出错误信息：MYSQL ERROR 2003 (HY000): Can’t connect to MySQL server on ‘192.168.1.X’ (111) 
    2. 如果按照如上所述设置，报出错误信息：Host ‘192.168.1.X’ is not allowed to connect to this MySQL server ，那么请检查数据库账号的权限是否有问题。

五、mysql5.7版本的请修改，/etc/mysql/mysql.conf.d，以下mysqld.cnf文件的配置

root@iZ2zeht3zvxbq5ycy698pwZ:/etc/mysql/mysql.conf.d# pwd
    /etc/mysql/mysql.conf.d

使用vi编辑mysqld.cnf

# The following can be used as easy to replay backup logs or for replication.
    # note: if you are setting up a replication slave, see README.Debian about
    # other settings you may need to change.
    #
    server-id               = 1
    log_bin                 = /var/log/mysql/mysql-bin.log
    expire_logs_days        = 10
    max_binlog_size   = 100M
    binlog_do_db            = test (这个地方是指定同步的数据库)
    binlog_ignore_db        = mysql（这个地方指定不同步的数据库）

六、重启一下mysql

root@iZ2zeht3zvxbq5ycy698pwZ:~# service mysql restart

七、通过以下命令验证二进制日志是否已经启动

八、mysql从库配置(135的那台机)，mysql5.7版本的请修改，/etc/mysql/mysql.conf.d，以下mysqld.cnf文件的配置  
从库只需要开启server-id就行了  
![这里写图片描述][70]  
如果要设置多个从库，则每个从库的server-id与主库和其他从库设置不同的唯一值。

九、mysql重启一下

root@iZ2ze451u03knf2sumyavyZ:~# service mysql restart

十、配置主从库之间的通信  
（105的那台机）查看 Master-Server ， binlog File 文件名称和 Position值位置 并且记下来

十一、要设置从库（135机）与主库（105机）进行通信，进行复制，使用必要的连接信息配置从库在从库上（135机）执行以下语句  
将选项值替换为与系统相关的实际值  
**参数格式，请勿执行**

mysql> CHANGE MASTER TO
        -> MASTER_HOST='master_host_name',     -> MASTER_USER='replication_user_name',     -> MASTER_PASSWORD='replication_password',     -> MASTER_LOG_FILE='recorded_log_file_name',     -> MASTER_LOG_POS=recorded_log_position;

**请将参数修改成符合你自己的，再执行**

mysql> CHANGE MASTER TO
        -> MASTER_HOST='39.107.227.105',     -> MASTER_USER='backup',     -> MASTER_PASSWORD='123456',     -> MASTER_LOG_FILE='mysql-bin.000001',     -> MASTER_LOG_POS=2021; Query OK, 0 rows affected, 2 warnings (0.03 sec)
    
    mysql>

下面这个参数对应于上图的Position

MASTER_LOG_POS=0 写成0 也是可以的

放在一行用逗号隔开，也是可以的

mysql> CHANGE MASTER TO MASTER_HOST='192.168.252.123', MASTER_USER='replication', MASTER_PASSWORD='mima', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=629;

十二、（135机）启动从数据库复制线程

mysql> START SLAVE;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql>

十三、（135机）查看复制状态

show slave status\G
    *************************** 1. row ***************************
     Slave_IO_State: Waiting for master to send event
     Master_Host: 39.107.227.105
     Master_User: backup
     Master_Port: 3306
     Connect_Retry: 60
     Master_Log_File: mysql-bin.000001
     Read_Master_Log_Pos: 2021
     Relay_Log_File: iZ2ze451u03knf2sumyavyZ-relay-bin.014660
     Relay_Log_Pos: 320
     Relay_Master_Log_File: mysql-bin.000001
     Slave_IO_Running: Yes
     Slave_SQL_Running: Yes
     Replicate_Do_DB: 
     ......

十四、（135）检查主从复制通信状态  
![这里写图片描述][70 1]  
![这里写图片描述][70 2]

Slave_IO_State #从站的当前状态 
    Slave_IO_Running： Yes #读取主程序二进制日志的I/O线程是否正在运行 
    Slave_SQL_Running： Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行。与I/O线程一样 
    Seconds_Behind_Master #是否为0，0就是已经同步了

**如果不是：**  
主要有以下 4 个方面：  
1、网络不通  
2、密码不对  
3、MASTER\_LOG\_POS 不对 ps  
4、mysql 的 auto.cnf server-uuid 一样（可能你是复制的mysql）

第4问题可照一下操作

root@iZ2ze451u03knf2sumyavyZ:~# find / -name 'auto.cnf'
    /var/lib/mysql/auto.cnf
    quit
    ^C
    root@iZ2ze451u03knf2sumyavyZ:~# vi /var/lib/mysql/auto.cnf

server-uuid=6b831bf3-8ae7-11e7-a178-000c29cb5cbc # 按照这个16进制格式，修改server-uuid，重启mysql service mysql restart ，并开启从库同步即可

root@iZ2ze451u03knf2sumyavyZ:~# service mysql restart
    root@iZ2ze451u03knf2sumyavyZ:~# mysql -uroot -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu)
    
    Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    mysql> START SLAVE
        -> quit;
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'quit' at line 2
    mysql> START SLAVE;
    Query OK, 0 rows affected, 1 warning (0.00 sec)

十五：通过第十步，查看到master是指定了同步的数据库和不同步的数据库。  
那么我们现在不想指定这两个参数。  
1.只需要回去将那两个变量给注释掉：

root@iZ2zeht3zvxbq5ycy698pwZ:/etc/mysql/mysql.conf.d# ls
    mysqld.cnf  mysqld_safe_syslog.cnf
    root@iZ2zeht3zvxbq5ycy698pwZ:/etc/mysql/mysql.conf.d# vi mysqld.cnf

2.重启主库（105机）

root@iZ2zeht3zvxbq5ycy698pwZ:/etc/mysql/mysql.conf.d# service mysql restart

3.登陆主库（105），查看主库状态

4.登陆从库（135），从新配置跟随的主库信息  
先停止线程

mysql> Stop SLAVE;
    Query OK, 0 rows affected (0.01 sec)

在从库（135中）配置同步主库（105）

mysql> CHANGE MASTER TO MASTER_HOST='39.107.227.105', MASTER_USER='backup', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;
    Query OK, 0 rows affected, 2 warnings (0.01 sec)

5.启动从库同步线程

mysql> START SLAVE;
    Query OK, 0 rows affected (0.01 sec)

十五、测试主从同步情况  
1.在主库中（105）在 Master-Server 创建测试库，使用创建的测试库，并创建表

mysql> CREATE DATABASE `replication_wwww.ymq.io`;
    Query OK, 1 row affected (0.00 sec)
    mysql> use `replication_wwww.ymq.io`;
    Database changed
    mysql> CREATE TABLE `sync_test` (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
    Query OK, 0 rows affected (0.02 sec)
    mysql>

2.查看从库（135机）Slave-Server 查看是否同步过来

同步完成。  
**后记：搞技术要的就是耐心和解决问题的能力，不要急，一步步慢慢来，祝您成功。**

[70]: /images/20220528/d9d327e22c5f4fb5a0cecfa947f9af65.png
[70 1]: /images/20220528/ea9c7d3f456341448b16301f2a3a0b10.png
[70 2]: /images/20220528/8136e7fd06684d389f34ea6bf9fb7797.png