mysql 主主复制配置
基础数据库安装
主主复制配置
何为主主复制？就是两个 mysql 都能读能写，数据记录通过二进制传达给对方从而保持数据的一致性。
（192.168.219.17 主从复制 +192.168.219.18 主从复制 ==192.168.219.17、192.168.219.17 主主复制）
因此主主复制中必须要解决的事情就是自增主键的问题。如果 mysql7 主键 id 增加到 17 了，此时二进制数据还没到达 mysql8，那么 mysql8 恰好要插入数据，那么新数据主键 id 也是 17，那不就是乱套了么！解决这一问题我们可以直接更改 MySQL 中的配置文件即可。
修改 /etc/my.cnf
--192.168.219.17：MySQL
server-id=17 #任意自然数n，只要保证两台MySQL主机不重复就可以了。
log-bin=mysql-bin #开启二进制日志
auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL
binlog-ignore-db=mysql #忽略mysql库【我一般都不写】
binlog-ignore-db=information_schema #忽略information_schema库【我一般都不写】
replicate-do-db=aa #要同步的数据库，默认所有库
--192.168.219.18：MySQL
server-id=18
log-bin=mysql-bin
auto_increment_increment=2
auto_increment_offset=2
replicate-do-db=aa
配置 192.168.219.17 主从复制
在 192.168.219.17 上创建 192.168.219.18 可以访问的用户
-- 创建用户（IP为可访问该master的IP，任意IP就写'%'）
mysql> CREATE USER 'mysql18slave'@'192.168.219.18' IDENTIFIED BY 'mysql@17_master_18_slave';
-- 分配权限（IP为可访问该 master的IP，任意IP就写'%'）
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql18slave'@'192.168.219.18';
-- 刷新权限
mysql>flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2146 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在 192.168.219.18 上配置
告知二进制文件名与位置
mysql> change master to
-> master_host='192.168.219.17',
-> master_user='mysql18slave',
-> master_password='mysql@17_master_18_slave',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=2146;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.219.17
Master_User: mysql17master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2146
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
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: 2146
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: 17
Master_UUID: 8a40d54b-14ff-11eb-b593-005056bb0c5f
Master_Info_File: /data/mysql/data/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)
配置 192.168.219.18 主从复制
在 192.168.219.18 上创建 192.168.219.17 可以访问的用户
-- 创建用户（IP为可访问该master的IP，任意IP就写'%'）
mysql> CREATE USER 'mysql17slave'@'192.168.219.17' IDENTIFIED BY 'mysql@18_master_17_slave';
-- 分配权限（IP为可访问该 master的IP，任意IP就写'%'）
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysql17slave'@'192.168.219.17';
-- 刷新权限
mysql>flush privileges;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2587 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在 192.168.219.17 上配置
告知二进制文件名与位置
mysql> change master to
-> master_host='192.168.219.18',
-> master_user='mysql17slave',
-> master_password='mysql@18_master_17_slave',
-> master_log_file='mysql-bin.000003',
-> master_log_pos=2587;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.219.18
Master_User: mysql18master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2587
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000003
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: 2587
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: 18
Master_UUID: a3d0fc8a-14ff-11eb-894f-005056bb7a82
Master_Info_File: /data/mysql/data/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)
测试是否成功
-- 在192.168.219.17上创建数据库
mysql> create database test17;
Query OK, 1 row affected (0.05 sec)
-- 在192.168.219.18上查询数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test17 |
+--------------------+
5 rows in set (0.00 sec)
-- 在192.168.219.18上创建数据库
mysql> create database test18;
Query OK, 1 row affected (0.00 sec)
-- 在192.168.219.17上查询数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test17 |
| test18 |
+--------------------+
6 rows in set (0.00 sec)
nginx 负载均衡访问
nginx 需要添加 stream 模块
参照
FastDFS+FastDHT 外网访问视频
中 nginx 指定配置安装模式
./configure --with-stream
nginx 负载均衡配置
user root;
worker_processes 2;
#error_log logs/error.log;
#error_log logs/error.log notice;
#error_log logs/error.log info;
pid /var/run/nginx/nginx.pid;
events {
worker_connections 65535;
}
stream{
server {
listen 3306;
proxy_pass mysqlpass;
}
upstream mysqlpass{
server 192.168.219.17:3306 weight=1;
server 192.168.219.18:3306 weight=1;
}
}
java 数据库访问配置
spring.datasource.url=jdbc:mysql://192.168.219.20:3306/test?useUnicode=true&characterEncoding=utf8&useCursorFetch=true&defaultFetchSize=500&allowMultiQueries=true&rewriteBatchedStatements=true&useSSL=false
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=@jfie&test+2020
my.cnf 配置示例
[mysqld]
[client]
port = 3306
socket = /data/mysql/data/mysql.sock
[mysqld]
server-id=23 #任意自然数n，只要保证两台MySQL主机不重复就可以了。
log-bin=mysql-bin #开启二进制日志
auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n
auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL
port = 3306
user = mysql
character-set-server = utf8mb4
default_storage_engine = innodb
log_timestamps = SYSTEM
socket = /data/mysql/data/mysql.sock
basedir = /opt/mysql
datadir = /data/mysql/data
pid-file = /data/mysql/data/mysql.pid
max_connections = 1000
max_connect_errors = 1000
table_open_cache = 1024
max_allowed_packet = 128M
open_files_limit = 65535
lower_case_table_names=1
#####====================================[innodb]==============================
innodb_buffer_pool_size = 1024M
innodb_file_per_table = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 2
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_max_dirty_pages_pct = 80
innodb_lock_wait_timeout = 30
innodb_data_file_path=ibdata1:1024M:autoextend
innodb_undo_tablespaces=3
#######====================================[log]==============================
log_error = /data/mysql/log/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/log/mysql-slow.log
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
通过 nginx 转发请求，注意相关端口都需要在防火墙中不拦截
对于已安装的 nginx，可以通过查询到对应的配置信息，在此基础上添加我们的配置重新编译覆盖安装就可以