LMLPHP后院

MySQL 主从同步复制实现步骤技术

maybe yes 发表于 2018-09-14 17:08

MySQL 主从复制手把手教程,本文以最精简的命令实现整个过程,不折腾,不冗长,真材实料。

💪💪💪

主从复制最好遵守从服务器版本不低于主服务器版本原则,MariaDBMySQL 之间也许会有一些问题(笔者测试过能正常复制)。整个过程遇到各种问题在所难免,本文基于安全的缘故,增加了 ssl 的步骤。

配置服务器

一些关键的配置项目

主:

[mysqld]

server-id=2
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1
ssl
ssl-ca=/var/lib/mysql/certs/ca-cert.pem
ssl-cert=/var/lib/mysql/certs/server-cert.pem
ssl-key=/var/lib/mysql/certs/server-key.pem
bind-address            = 0.0.0.0

[mysqld]

server-id=3
replicate_wild_do_table=lmlphp.%
replicate_wild_do_table=db_prefix%.%

创建证书

在 /var/lib/mysql 下 创建文件夹 certs,这里需要注意的是 MySQL 生成证书输入的所有信息都必须一致,MariaDB 和 MySQL 不同的是输入的用户信息服务端和客户端不能一致。如下:

openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca-cert.pem

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

chown -R mysql.mysql /var/lib/mysql/certs

复制证书到从服务器

把 ca-cert.pem,client-cert.pem,client-key.pem 复制到从服务器上的 /var/log/mysql/certs/repl 下

scp -p master:/var/lib/mysql/certs/* slave:/var/lib/mysql/certs/repl/

主服务器上创建复制账户

CREATE USER 'repl_all'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl_all'@'%' require ssl;
FLUSH PRIVILEGES;

重启服务

在主从服务器分别执行(mysql 这点做的很不好,必须重启才能生效)

service mysql restart

创建备份数据

推荐使用 mysqldump 生成

mysqldump --all-databases --default-character-set=utf8mb4 --master-data > dump.db
// or
mysqldump --databases db1 db2 --default-character-set=utf8mb4 --master-data > dump.db

使用 vim 或者 head 查看主服务信息

在 dump.db 文件中找出主服务的信息,日志文件和位置

head -n 100 dump.db | less

数据传给从服务器

将 dump.db 传给从服务器,然后导入到 mysql 中,使用 source 导入

source dump.db

CHANGE MASTER TO

在从服务器上执行,修改成对应 IP,密码,文件,位置

change master to master_host='119.119.119.119', master_user='repl_all', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=312065, master_ssl=1, master_ssl_ca='/var/lib/mysql/certs/repl/ca-cert.pem', master_ssl_cert='/var/lib/mysql/certs/repl/client-cert.pem',master_ssl_key='/var/lib/mysql/certs/repl/client-key.pem';

启动复制

在从库上执行

start slave

查看状态

到此,同步工作已经完成。查看从服务器:

MariaDB > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 119.119.119.119
                  Master_User: repl_all
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 550149
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 215940
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: lmlphp.%,db_prefix%.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 550149
              Relay_Log_Space: 216239
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /var/lib/mysql/certs/repl/ca-cert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /var/lib/mysql/certs/repl/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /var/lib/mysql/certs/repl/client-key.pem
        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: 2
               Master_SSL_Crl: /var/lib/mysql/certs/repl/ca-cert.pem
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
      Replicate_Do_Domain_Ids: 
  Replicate_Ignore_Domain_Ids: 
                Parallel_Mode: conservative
1 row in set (0.00 sec)

MariaDB > show processlist;
+----+-------------+-----------+----------------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
| Id | User        | Host      | db             | Command | Time | State                                                                       | Info             | Progress |
+----+-------------+-----------+----------------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|  2 | root        | localhost | lmlphp         | Query   |    0 | init                                                                        | show processlist |    0.000 |
|  3 | system user |           | NULL           | Connect |  167 | Waiting for master to send event                                            | NULL             |    0.000 |
|  4 | system user |           | NULL           | Connect |    4 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
+----+-------------+-----------+----------------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
3 rows in set (0.00 sec)

查看主服务器

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

mysql> show processlist;
+-----+----------+------------------------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
| Id  | User     | Host                               | db     | Command     | Time | State                                                         | Info             |
+-----+----------+------------------------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
|   2 | root     | localhost                          | lmlphp | Query       |    0 | starting                                                      | show processlist |
| 664 | repl_all | 119.119.119.119:34330              | NULL   | Binlog Dump |  194 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+-----+----------+------------------------------------+--------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

半同步复制 semi

半同步复制是 Google 开发的 MySQL 插件,笔者认为,半同步复制应用场景不多,并不适合中小企业。半同步复制必须等待至少一个从服务器同步完成才提交事务,这种机制在大多数情况下是不适用的。有些问题,可以从应用层面乃至业务层面去避免。

2024-03-29 21:55:35 1711720535 0.033120