1. 介绍
在企业中，数据库高可用一直是企业的重中之重，中小企业很多都是使用mysql主从方案，一主多从，读写分离等，但是单主存在单点故障，从库切换成主库需要作改动。因此，如果是双主或者多主，就会增加mysql入口，增加高可用。不过多主需要考虑自增长ID问题，这个需要特别设置配置文件，比如双主，可以使用奇偶，总之，主之间设置自增长ID相互不冲突就能完美解决自增长ID冲突问题。2. MySQL主主
MySQL的主主，其实就是相互主从。上一节搭建好了主从。在原来的基础上再反过来搭建主从。3. MySQL主主复制的原理
相互复制，相互主从彼此都是对方的主，彼此都是对方的从4. 环境准备
4.1环境
4.2 配置准备
4.2.1 master准备¶
开启二进制日志
配置唯一的server-id
获得master二进制日志文件名及位置
创建一个用于slave和master通信的用户账号
4.2.2 slave准备¶
配置唯一的server-id
使用master分配的用户账号读取master二进制日志
启用slave服务5. master 操作
5.1 修改master配置文件¶
找到主数据库的配置文件my.cnf(或者my.ini)，我的在/etc/mysql/my.cnf,在[mysqld]部分插入如下两行：
root@manage01:~# egrep -irn 'log_bin|server-id' /etc/mysql/mysql.conf.d/mysqld.cnf
84:server-id = 2
85:log_bin = /var/log/mysql/mysql-bin.log
配置完毕后，重启mysql
root@manage01:~# /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
5.2 授权同步账号¶
创建用户并授权：用户：repl，密码：repl
grant all privileges on *.* to repl@'%' identified by 'repl';
操作过程
mysql> select host,user from mysql.user;
+-------------+------------------+
| host | user |
+-------------+------------------+
| % | loocha |
| 192.168.5.% | loocha |
| localhost | airflow |
| localhost | debian-sys-maint |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
| localhost | seafile |
+-------------+------------------+
8 rows in set (0,00 sec)
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'repl';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user from mysql.user;
+-------------+------------------+
| host | user |
+-------------+------------------+
| % | loocha |
| % | repl |
| 192.168.5.% | loocha |
| localhost | airflow |
| localhost | debian-sys-maint |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
| localhost | seafile |
+-------------+------------------+
9 rows in set (0,00 sec)
5.3 记录状态¶
# 查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 434 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0,00 sec)
注意
注意这个文件mysql-bin.000001和点4346. slave 操作
6.1 修改master配置文件¶
找到主数据库的配置文件my.cnf(或者my.ini)，修改server-id。 也就是之前的主，已经配置为server-id=1
6.2 链接¶
6.2.1 快速命令¶
登录到从上操作(master mysql的leco主机上操作)
stop slave;
grant all privileges on *.* to repl@'%' identified by 'repl';
change master to master_host='192.168.5.110',\
master_port=3306,\
master_user='repl',\
master_password='repl',\
master_log_file='mysql-bin.000001',\
master_log_pos=1571;
start slave;
show slave status\G;
操作过程
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.2.146',\
-> master_port=3306,\
-> master_user='repl',\
-> master_password='repl',\
-> master_log_file='mysql-bin.000001',\
-> master_log_pos=434;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
6.2.2 查看状态¶
登录到从上操作(master mysql的leco主机上操作)，在从上查看mysql状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.146
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 434
Relay_Log_File: leco-relay-bin.000002
Relay_Log_Pos: 320
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:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 434
Relay_Log_Space: 526
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: 2
Master_UUID: 28d41b5e-5341-11e8-b65a-f4ec380008c2
Master_Info_File: /var/lib/mysql/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)
ERROR:
No query specified
以上看到两个yes一般都是主从OK。
6.2.3 设置¶
leco机器上mysql配置
auto_increment_offset = 1
auto_increment_increment = 2
manage01机器上mysql配置
auto_increment_increment = 2
auto_increment_offset = 2
为了保证id不冲突，我配置以上两个参数，执行过程如下
root@leco:/etc/mysql/mysql.conf.d# egrep -v '#|^$' mysqld.cnf
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
auto_increment_offset = 1
auto_increment_increment = 2
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
[mysql]
auto-rehash
root@leco:/etc/mysql/mysql.conf.d# /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
root@manage01:/etc/mysql/mysql.conf.d# egrep -v '#|^$' mysqld.cnf
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
character-set-server=utf8
auto_increment_offset = 2
auto_increment_increment = 2
bind-address = 0.0.0.0
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover-options = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /var/log/mysql/error.log
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
root@manage01:/etc/mysql/mysql.conf.d# /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.7. 测试
7.1 leco主机添加主数据¶
root@leco:~# mysql -uroot -p
Enter password:
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.25-0ubuntu0.16.04.2-log (Ubuntu)
Copyright (c) 2000, 2019, 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> use summer;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> drop table cmz;
ERROR 1051 (42S02): Unknown table 'summer.cmz'
mysql> create table cmz(
-> id int not null primary key auto_increment,
-> name varchar(20),
-> age int not null,
-> score int not null
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into cmz(name,age,score) values("cmz from leco data",18,88);
Query OK, 1 row affected (0.01 sec)
mysql> insert into cmz(name,age,score) values("cmz1 from leco data",40,88);
Query OK, 1 row affected (0.01 sec)
mysql> select * from cmz;
+----+---------------------+-----+-------+
| id | name | age | score |
+----+---------------------+-----+-------+
| 1 | cmz from leco data | 18 | 88 |
| 3 | cmz1 from leco data | 40 | 88 |
+----+---------------------+-----+-------+
2 rows in set (0.00 sec)
7.2 manage01主机查看数据¶
root@manage01:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.23-0ubuntu0.16.04.1-log (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> use summer
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from cmz;
+----+---------------------+-----+-------+
| id | name | age | score |
+----+---------------------+-----+-------+
| 1 | cmz from leco data | 18 | 88 |
| 3 | cmz1 from leco data | 40 | 88 |
+----+---------------------+-----+-------+
2 rows in set (0,00 sec)
这样来看leco主机上添加的数据同步到manage01主机上了。
7.3 manage01主机天添加数据¶
root@manage01:~# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
root@manage01:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.23-0ubuntu0.16.04.1-log (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> use summer;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into cmz(name,age,score) values("cmz2 from manage01",18,66);
Query OK, 1 row affected (0,13 sec)
mysql> insert into cmz(name,age,score) values("cmz3 from manage01",40,66);
Query OK, 1 row affected (0,16 sec)
mysql> select * from cmz;
+----+---------------------+-----+-------+
| id | name | age | score |
+----+---------------------+-----+-------+
| 1 | cmz from leco data | 18 | 88 |
| 3 | cmz1 from leco data | 40 | 88 |
| 4 | cmz2 from manage01 | 18 | 66 |
| 6 | cmz3 from manage01 | 40 | 66 |
+----+---------------------+-----+-------+
4 rows in set (0,00 sec)
7.4 leco主机查看数据¶
root@leco:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.25-0ubuntu0.16.04.2-log (Ubuntu)
Copyright (c) 2000, 2019, 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> use summer;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from cmz;
+----+---------------------+-----+-------+
| id | name | age | score |
+----+---------------------+-----+-------+
| 1 | cmz from leco data | 18 | 88 |
| 3 | cmz1 from leco data | 40 | 88 |
| 4 | cmz2 from manage01 | 18 | 66 |
| 6 | cmz3 from manage01 | 40 | 66 |
+----+---------------------+-----+-------+
4 rows in set (0.00 sec)
这样来看manage01主机上添加的数据同步到leco主机上了。到此为止，双主就搞好了。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
