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库的日志同步，否侧 数据 会全部丢失
登录 mysqlA
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数据库双主负载搭建成功
