mysql双主搭建
mysql双主搭建
summerking
发布于 2022-09-16 04:02:48
发布于 2022-09-16 04:02:48
代码可运行
运行总次数：0
代码可运行
之前summer部署过主从，这里记录下部署双主的方法，坑有蛮多但总体还算顺利。MySQL版本为8.0.19。
# 前置条件
- MySQL版本为
8.0.19安装方法见mysql8安装 - 这里准备两个节点A:
200,B:201
root in summer-200 in /usr/local/mysql took 34m44s
➜ mysql -V
mysql Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
root in summer-200 in /usr/local/mysql
➜ ip a | grep 200
inet 192.168.163.200/16 brd 192.168.255.255 scope global ens192root in summer-201 in ~
➜ mysql -V
mysql Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
root in summer-201 in ~
➜ ip a | grep 201
inet 192.168.163.201/16 brd 192.168.255.255 scope global ens192
# 双主配置
# 主机A（200）配置
root in summer-200 in ~ via 🐍 v2.7.5 took 15m21s
➜ cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=23306
user=mysql
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#skip-grant-tables
# 双主配置开始
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
# 表名不区分大小写
# lower_case_table_names=1
#任意自然n,只需要保证两台mysql主机不重复就可以
server-id = 200
#开启二进制日志
log-bin=mysql-bin
#binlog单文件最大值
max_binlog_size=1024M
#起始值，一般填写第n台主机mysql.此时为第一台主 mysql
auto_increment_offset=1
#步进值auto_imcrement 。一般有n台主mysql就填n
auto_increment_increment=2
#指定同步的数据库，不填写则默认所有的数据库
#replicate-do-db=gohealth-plat
log-slave-updates=true
secure_file_priv=/
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
protocol=tcp
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=23306
default-character-set=utf8
# 双主配置结束
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
root in summer-200 in ~ via 🐍 v2.7.5
➜# 配置后重启mysql
root in summer-200 in local/mysql/data
➜ vim /etc/my.cnf
root in summer-200 in local/mysql/data took 17s
➜ systemctl restart mysqld.service
root in summer-200 in local/mysql/data took 5s
➜ systemctl status mysqld.service
● mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (running) since Mon 2021-05-17 11:56:19 CST; 31s ago
Docs: man:systemd-sysv-generator(8)
Process: 2410510 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
Process: 2410827 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
Tasks: 40
Memory: 372.6M
CGroup: /system.slice/mysqld.service
├─2410845 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/summer-200.pid
└─2411230 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mariadb/ma...
May 17 11:56:17 summer-200 systemd[1]: Starting LSB: start and stop MySQL...
May 17 11:56:19 summer-200 mysqld[2410827]: Starting MySQL..[ OK ]
May 17 11:56:19 summer-200 systemd[1]: Started LSB: start and stop MySQL.# 主机B（201）配置
root in summer-201 in ~ took 10m31s
➜ cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port=23306
user=mysql
socket=/tmp/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
#skip-grant-tables
# 双主配置开始
# 允许最大连接数
max_connections=10000
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
# 表名不区分大小写
#lower_case_table_names=1
#任意自然n,只需要保证两台mysql主机不重复就可以
server-id = 201
#开启二进制日志
log-bin=mysql-bin
#binlog单文件最大值
max_binlog_size=1024M
#起始值，一般填写第n台主机mysql.此时为第二台主 mysql
auto_increment_offset=2
#步进值auto_imcrement 。一般有n台主mysql就填n
auto_increment_increment=2
#指定同步的数据库，不填写则默认所有的数据库
#replicate-do-db=gohealth-plat
log-slave-updates=true
secure_file_priv=/
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
protocol=tcp
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=23306
default-character-set=utf8
# 双主配置结束
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d# 配置后重启mysql
root in summer-201 in ~
➜ vim /etc/my.cnf
root in summer-201 in ~ took 14s
➜ systemctl restart mysqld.service
root in summer-201 in ~ took 5s
➜ systemctl status mysqld.service
● mysqld.service - LSB: start and stop MySQL
Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled)
Active: active (running) since Mon 2021-05-17 11:56:47 CST; 13s ago
Docs: man:systemd-sysv-generator(8)
Process: 2136078 ExecStop=/etc/rc.d/init.d/mysqld stop (code=exited, status=0/SUCCESS)
Process: 2136492 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS)
Tasks: 40
Memory: 380.5M
CGroup: /system.slice/mysqld.service
├─2136505 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/summer-201.pid
└─2136862 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mariadb/ma...
May 17 11:56:45 summer-201 systemd[1]: Stopped LSB: start and stop MySQL.
May 17 11:56:45 summer-201 systemd[1]: Starting LSB: start and stop MySQL...
May 17 11:56:47 summer-201 mysqld[2136492]: Starting MySQL..[ OK ]
May 17 11:56:47 summer-201 systemd[1]: Started LSB: start and stop MySQL.# 主机A（200）sql配置
- 在A上创建复制用户test，密码hadoop
- 记录二进制文件名和位置
root in summer-200 in ~ via 🐍 v2.7.5
➜ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> create user 'test'@'192.168.163.201' identified with mysql_native_password by 'hadoop';
Query OK, 0 rows affected (0.03 sec)
mysql> grant replication slave on *.* to 'test'@'192.168.163.201';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication client on *.* to 'test'@'192.168.163.201';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'192.168.163.201';
+--------------------------------------------------------------------------------+
| Grants for test@192.168.163.201 |
+--------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `test`@`192.168.163.201` |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1108 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)- 登陆主机B（201）数据库，执行同步语句
mysql> change master to
-> master_host='192.168.163.200',
-> master_user='test',
-> master_port=23306,
-> master_password='hadoop',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=1108;
Query OK, 0 rows affected, 1 warning (0.01 sec)
--------------
change master to
master_host='192.168.163.200',
master_user='test',
master_port=23306,
master_password='hadoop',
master_log_file='mysql-bin.000002',
master_log_pos=1108;
--------------# 主机B（201）sql配置
- 在B上创建复制用户test，密码hadoop
- 记录二进制文件名和位置
root in summer-201 in ~
➜ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, 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> change master to
-> master_host='192.168.163.200',
-> master_user='test',
-> master_port=23306,
-> master_password='hadoop',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=1108;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> create user 'test'@'192.168.163.200' identified with mysql_native_password by 'hadoop';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'test'@'192.168.163.200';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication client on *.* to 'test'@'192.168.163.200';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test'@'192.168.163.200';
+--------------------------------------------------------------------------------+
| Grants for test@192.168.163.200 |
+--------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO `test`@`192.168.163.200` |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1108 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)- 登陆主机A（200）数据库，执行同步语句
mysql> change master to
-> master_host='192.168.163.201',
-> master_user='test',
-> master_port=23306,
-> master_password='hadoop',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=1108;
Query OK, 0 rows affected, 1 warning (0.02 sec)
--------------
change master to
master_host='192.168.163.201',
master_user='test',
master_port=23306,
master_password='hadoop',
master_log_file='mysql-bin.000001',
master_log_pos=1108;
--------------# 启动主机主从复制
- 主机A（200）数据库，执行启动语句
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.163.201
Master_User: test
Master_Port: 23306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1108
Relay_Log_File: summer-200-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: gohealth-plat
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: 1108
Relay_Log_Space: 539
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: 201
Master_UUID: 0d65fe59-b6ba-11eb-802f-000c295c9feb
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
mysql> \q
Bye- 主机B（201）数据库，执行启动语句
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.163.200
Master_User: test
Master_Port: 23306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1108
Relay_Log_File: summer-201-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: gohealth-plat
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: 1108
Relay_Log_Space: 539
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: 200
Master_UUID: f2364547-b6b2-11eb-9943-000c290a6516
Master_Info_File: mysql.slave_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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)
mysql> \q
Bye# 验证
# 建库是否同步
- 在B新建test库
mysql> CREATE DATABASE `test` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.02 sec)1
2
3
4
5
6
7
8
9
10
11
12
13
14
- 查看A是否同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.02 sec)- 反之亦然（在A新建库summer，查看B是否同步）
在A新建库summer
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.02 sec)
mysql> CREATE DATABASE `summer` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| summer |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)查看B
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| summer |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)# 插入数据是否同步
- 在A中test库随便建个表写一些数据
mysql> use test;
Database changed
mysql> create table students(
-> id int(11) not null auto_increment,
-> name varchar(20) not null,
-> nickname varchar(20) default null,
-> sex char(1) default null,
-> in_time datetime default null ,
-> primary key(id)
-> )ENGINE=InnoDB AUTO_INCREMENT=2, DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> show tables
-> ;
+----------------+
| Tables_in_test |
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
mysql> insert into students value (2,'李四','四哥','男',now());
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+----+--------+----------+------+---------------------+
| id | name | nickname | sex | in_time |
+----+--------+----------+------+---------------------+
| 2 | 李四 | 四哥 | 男 | 2021-05-17 14:32:00 |
+----+--------+----------+------+---------------------+
1 row in set (0.00 sec)
mysql>
- 查看B中test库是否同步该表以及表中数据
mysql> use test;
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
mysql> select * from students;
+----+--------+----------+------+---------------------+
| id | name | nickname | sex | in_time |
+----+--------+----------+------+---------------------+
| 2 | 李四 | 四哥 | 男 | 2021-05-17 14:32:00 |
+----+--------+----------+------+---------------------+
1 row in set (0.00 sec)
mysql>- 反之亦然（在B新建表并插入数据，查看A是否同步）
在B新建表mytable并插入数据
mysql> create table mytable (name varchar(20), sex char(1),birth date, birthaddr varchar(20));
Query OK, 0 rows affected (0.11 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mytable |
| students |
+----------------+
2 rows in set (0.13 sec)
mysql> describe mytable;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| birthaddr | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> insert into mytable
-> values(
-> 'abc','f','1988-07-07','chian');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mytable;
+------+------+------------+-----------+
| name | sex | birth | birthaddr |
+------+------+------------+-----------+
| abc | f | 1988-07-07 | chian |
+------+------+------------+-----------+
1 row in set (0.00 sec)
mysql>查看A
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mytable |
| students |
+----------------+
2 rows in set (0.01 sec)
mysql> select * from mytable;
+------+------+------------+-----------+
| name | sex | birth | birthaddr |
+------+------+------------+-----------+
| abc | f | 1988-07-07 | chian |
+------+------+------------+-----------+
1 row in set (0.04 sec)
mysql>
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2021-05-17，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
