Mysql 主从/双主同步

主从同步

一、准备阶段:

#共3台服务器，2台安装mysql，1台nginx（主从同步不用nginx，双主需要nginx）
#这里默认mysqlA为主库（有数据），mysqlB为从库（无数据），mysqlA无数据库也是可以的
mysqlA    mysqlB     nginx

二、登录mysqlA和mysqlB修改 my.cnf 配置

server_id = 1
#这个配置不能 重复，可以随意命名 为了方便顺序起  mysqlA id 1   mysqlA id 2

#开启bin日志

log-bin= mysql-bin

#开启bin日志级别 有三种 我常用 row 行级别 更详细日志
binlog-format=ROW

#忽略 需要同步的表 #也可以配置 只同步的表 replicate-do-db=test
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema

#双主都是0，可读写，1是可读
read-only=0
relay_log=mysql-relay-bin
log-slave-updates=on

##初始id值mysqlA为 1 ，mysqlA为 2
auto-increment-offset=1

##自增步幅度 2，因为负载2台所以这边设置2，根据自己的要求
auto-increment-increment=2

#注意：如果这个参数不同，可能会存在问题
lower_case_table_names=0  #表明区分大小写
lower_case_table_names=1  #表名不区分大小写

#选择增加配置： slave-skip-errors=all  #跳过所有错误

完整配置参考：

#mysqlA配置
server_id = 1
log-bin= mysql-bin
binlog-format=ROW
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#slave-skip-errors=all

read-only=0
relay_log=mysql-relay-bin
log-slave-updates=on
auto-increment-offset=1
auto-increment-increment=2

#mysqlB配置
server_id = 2
log-bin= mysql-bin
binlog-format=ROW
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#slave-skip-errors=all

read-only=0
relay_log=mysql-relay-bin
log-slave-updates=on
auto-increment-offset=2
auto-increment-increment=2

三、重启两台数据库

systemctl restart mysql
#在挂载的data目录里面有mysql-bin.index和类似命名mysql-bin.0000001表示开启日志成功

四、开始同步

注意：开启同步操作必须先优先从库读主库

举例：mysqlA (数据有) mysqlB(数据没有) 则必须先操作B库，去读 A库的日志同步，否侧数据会全部丢失

mysql -u root -p
mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000022 | 55789429 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

##需要记录,然后登录 mysqlB开启同步操作
file   mysql-bin.000022    pos  55789429 #根据实际填写

登录mysqlB

#读取远程bin日志开启同步

mysql> change master to master_host='mysqlA的ip',  master_port=3306, master_user='root',  master_password='Simple@01!',  master_log_file='mysql-bin.000022',  master_log_pos=55789429 ;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

#查看是否成功
mysql> show slave status\\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.115
                  Master_User: simple
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000022
          Read_Master_Log_Pos: 45299492
               Relay_Log_File: mysql-relay-bin.000004
                Relay_Log_Pos: 55236
        Relay_Master_Log_File: mysql-bin.000022
             Slave_IO_Running: Yes   #看这个状态 yes为成功
            Slave_SQL_Running: Yes   #看这个状态 yes为成功
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema
           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: 45299492
              Relay_Log_Space: 55443
              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: 115
                  Master_UUID: a22728a4-9520-11ec-96f4-0050568d79d0
             Master_Info_File: /home/dpan/mysqldata/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)

至此主从已经搭建完毕，可以在mysqlA添加删除或者修改一条数据，看mysqlB是否实时更新。

双主同步

需要mysqlA读mysqlB的日志，命令一样这边就不写明了

#在mysqlB读取
mysql> show master status;

#登录mysqlA
mysql> change master to master_host='mysqlB的ip',  master_port=3306, master_user='root',  master_password='Simple@01!',  master_log_file='mysql-bin.000001',  master_log_pos=154; #根据实际情况
start slave;
Query OK, 0 rows affected (0.01 sec)

现在双主已经搭建完毕

nginx负载

现在就可以用nginx 做负载，登录nginx服务器修改配置

stream  {
    upstream mysql {
        ##负载 下面的 数据库
        server mysqlA的ip:3306 max_fails=3 fail_timeout=30s;
        server mysqlB的ip:3306 max_fails=3 fail_timeout=30s;
    }

    server {
        ## 负载端口
        listen 3306;
        proxy_connect_timeout 30s;
        proxy_timeout 120s;
        proxy_pass mysql;
    }

}

#重载
nginx -s reload

数据库双主负载搭建成功