Ubuntu16.04 Mysql5.7 Gtid模式的主从复制集群部署

安装环境

操作系统	ubuntu16.04
Mysql	5.7.33
CPU	8
MEM	16G
SYS Disk	60G
DATA Disk	500G

网络规划

ROLE	HOSTNAME	IP
master	mysql-01	172.16.199.78
slave	mysql-02	172.16.199.109
slave	mysql-03	172.16.199..131

系统初始化（所有节点）

1.时区设置

#设置时区为上海
timedatectl set-timezone Asia/Shanghai
#查看是否修改成功
timedatectl status

2.时间同步

#修改NTP服务器为阿里云
sed -ri '/^#NTP/s/(.*)(=.*)/NTP=ntp.aliyun.com/' /etc/systemd/timesyncd.conf
sed -ri '/^#FallbackNTP/s/(.*)(=.*)/FallbackNTP=ntp1.aliyun.com/' /etc/systemd/timesyncd.conf
#重启时间同步服务
systemctl restart systemd-timesyncd.service

3.数据盘处理

#格式化
mkfs.ext4 /dev/sdb
#编辑fstab,增加内容
vim /etc/fstab
/dev/sdb	/var/lib/mysql	ext4	defaults	0	2
#临时挂载到/mnt
mount /dev/sdb /mnt

安装部署

4.安装Mysql Master节点

#安装mysql-server，安装完成后会提示为root设置密码，根据提示完成。
apt-get install mysql-server -y
#mysql安全设置，输入刚才设置好的密码。
mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!
#编辑mysqld.cnf
vim /etc/mysql/mysql.conf.d/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
bind-address		= 172.16.199.78
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
max_connections        = 3000
query_cache_limit	= 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id		= 1
log_bin=master-binlog
expire_logs_days	= 10
max_binlog_size   = 100M
binlog_format = row
gtid_mode = on
enforce_gtid_consistency = on
character-set-server = utf8
collation-server = utf8_general_ci
#停止mysql服务
systemctl stop mysql.service
#移动数据目录文件
mv /var/lib/mysql/* /mnt/
#卸载/mnt
umount /mnt
#挂在数据盘到/var/lib/mysql
mount -a
#修改权限
chown mysql:mysql /var/lib/mysql
chmod 770 /var/lib/mysql
#启动mysql服务
systemctl start mysql.service

5.Master节点创建复制账号

mysql -uroot -p
#查看gtid模式是否启动成功，有Executed_Gtid_Set表示成功
mysql> show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+----------------------+----------+--------------+------------------+------------------------------------------+
| master-binlog.000001 |      168 |              |                  | c60a8665-7738-11ec-bd3c-fa163e12cffd:1-7 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

#设置复制账号
mysql> grant replication slave, replication client on *.* to 'repl'@'%' identified by 'repl';

6.安装Mysql Slave节点

#安装mysql-server，安装完成后会提示为root设置密码，根据提示完成。
apt-get install mysql-server -y
#mysql安全设置，输入刚才设置好的密码，过程与master节点设置一样。
mysql_secure_installation
#编辑mysql-02的mysqld.cnf
vim /etc/mysql/mysql.conf.d/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
bind-address		= 172.16.199.109
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
max_connections        = 3000
query_cache_limit	= 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id		= 2
log_bin			= slave-binlog
expire_logs_days	= 10
max_binlog_size   = 100M
binlog_format = row
gtid_mode = on
enforce_gtid_consistency = on
character-set-server = utf8
collation-server = utf8_general_ci
#编辑mysql-03的mysqld.cnf
vim /etc/mysql/mysql.conf.d/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
bind-address		= 172.16.199.131
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover-options  = BACKUP
max_connections        = 3000
query_cache_limit	= 1M
query_cache_size        = 16M
log_error = /var/log/mysql/error.log
server-id		= 3
log_bin			= slave-binlog
expire_logs_days	= 10
max_binlog_size   = 100M
binlog_format = row
gtid_mode = on
enforce_gtid_consistency = on
character-set-server = utf8
collation-server = utf8_general_ci
#停止mysql服务
systemctl stop mysql.service
#移动数据目录文件
mv /var/lib/mysql/* /mnt/
#卸载/mnt
umount /mnt
#挂在数据盘到/var/lib/mysql
mount -a
#修改权限
chown mysql:mysql /var/lib/mysql
chmod 770 /var/lib/mysql
#启动mysql服务
systemctl start mysql.service

7.Slave节点启动复制

mysql -uroot -p
#设置master连接信息
mysql> CHANGE MASTER TO MASTER_HOST='172.16.199.78', MASTER_USER='repl', MASTER_PASSWORD='repl', MASTER_AUTO_POSITION = 1;
#启动复制
mysql> start slave;
#查看复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.199.78
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000001
          Read_Master_Log_Pos: 168
               Relay_Log_File: mysql-02-relay-bin.000005
                Relay_Log_Pos: 1039
        Relay_Master_Log_File: master-binlog.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: 168
              Relay_Log_Space: 1340
              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: 1
                  Master_UUID: c60a8665-7738-11ec-bd3c-fa163e12cffd
             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: c60a8665-7738-11ec-bd3c-fa163e12cffd:1-7
            Executed_Gtid_Set: 7f6ecc56-776d-11ec-bf69-fa163e4b3586:1-4,
c60a8665-7738-11ec-bd3c-fa163e12cffd:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)