Mysql Replication
一、环境
Role | HostName | IP | Version | Port | Server_id | Database | Basedir | Datadir |
---|---|---|---|---|---|---|---|---|
Master | server01 | 192.168.1.191 | Mysql 5.7 | 3307 | 1 | mydb | /usr/local/mysql | /data/mysql/data |
Slave | server02 | 192.168.1.192 | Mysql 5.7 | 3308 | 2 | mydb | /usr/local/mysql | /data/mysql/data |
二、安装Master数据库
1.关闭防火墙
配置主从需关闭防火墙或开通3307 3308端口
关闭防火墙:
[root@server02 ~]# systemctl status firewalld.service
[root@server02 ~]# systemctl stop firewalld.service
[root@server02 ~]# systemctl disable firewalld.service
开通端口:
[root@server01 ~]# firewall-cmd --zone=public --add-port=3307/tcp --permanent
[root@server01 ~]# firewall-cmd --reload
1.配置YUM
[root@mysql opt]# cp -r /run/media/admin/CentOS\ 7\ x86_64/* /opt/yum
[root@mysql opt]# vi /etc/yum.repos.d/CentOS-Base.repo
[CentOS-Base]
baseurl=file:///opt/yum
gpgcheck=0
enabled=1
2.安装操作系统依赖包
For MySQL 5.7.19 and later: Support for Non-Uniform Memory Access
(NUMA) has been added to the generic Linux build, which has a dependency
now on the libnuma library; if the library has not been installed on your
system, use you system's package manager to search for and install it (see
the preceding item for some sample commands).
SLES 11: As of MySQL 5.7.19, the Linux Generic tarball package format is
EL6 instead of EL5. As a side effect, the MySQL client bin/mysql needs
libtinfo.so.5.
A workaround is to create a symlink, such as
ln -s libncurses.so.5.6 /lib64/libtinfo.so.5 on 64-bit systems or
ln-s libncurses.so.5.6 /lib/libtinfo.so.5 on 32-bit systems.
[root@mysql soft]# yum install libaio
[root@mysql soft]# yum install libnuma
3.配置数据磁盘
磁盘挂载:
[root@mysql soft]# mkfs.xfs /dev/sdb
meta-data=/dev/sdb isize=512 agcount=4, agsize=1310720 blks
= sectsz=512 attr=2, projid32bit=1
= crc=1 finobt=0, sparse=0
data = bsize=4096 blocks=5242880, imaxpct=25
= sunit=0 swidth=0 blks
naming =version 2 bsize=4096 ascii-ci=0 ftype=1
log =internal log bsize=4096 blocks=2560, version=2
= sectsz=512 sunit=0 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
[root@mysql soft]# blkid
/dev/sda1: UUID="28c5912e-d7ae-4c5f-a404-f1806d48ca93" TYPE="xfs"
/dev/sda2: UUID="VczXPR-tZhP-TR58-NYzW-3hsR-F3Qc-TzTaO6" TYPE="LVM2_member"
/dev/sr0: UUID="2020-11-04-11-36-43-00" LABEL="CentOS 7 x86_64" TYPE="iso9660" PTTYPE="dos"
/dev/sdb: UUID="804f4903-7d62-4669-b48a-5add1d0d2312" TYPE="xfs"
/dev/mapper/centos-root: UUID="6947bf69-372b-4127-b1c9-287c133c0090" TYPE="xfs"
[root@mysql soft]# vi /etc/fstab
UUID="804f4903-7d62-4669-b48a-5add1d0d2312" /data xfs defaults 0 0
[root@mysql soft]# mount -a
4.卸载Mariadb
[root@mysql mysql]# rpm -qa |grep mariadb
[root@mysql mysql]# yum remove mariadb-libs-5.5.68-1.el7.x86_64
5.创建用户和组
[root@server01 ~]# groupadd -g 600 mysql
[root@server01 ~]# useradd -r -g mysql -u 600 -s /bin/false mysql
温馨提示: -r参数指的是创建系统用户,该用户没有登录权限。
[root@mysql ~]# id mysql
uid=600(mysql) gid=600(mysql) groups=600(mysql)
6.解压安装包
[root@server02 soft]# tar zxvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
温馨提示:如果tar不支持z选项,则可以使用gunzip命令
gunzip < mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz |tar xvf -
7.创建软件链接
[root@mysql soft]# cd /usr/local/
[root@server02 local]# ln -s mysql-5.7.26-linux-glibc2.12-x86_64 mysql
8.添加PATH环境变量
[root@mysql mysql]# vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
温馨提示: 也可以使用sed命令添加
[root@mysql mysql]# sed -i '$a export PATH=/usr/local/mysql/bin:$PATH\n' /etc/profile
[root@mysql mysql]# source /etc/profile
[root@mysql mysql]# tail -2 /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
9.创建数据文件目录
[root@mysql soft]# cd /usr/local/mysql
[root@mysql mysql]# mkdir mysql-files
[root@mysql mysql]# chown mysql:mysql mysql-files
[root@mysql mysql]# chmod 750 mysql-files
10.初如化目录
--initialize生成一个临时密码:
- 密码12位数字,4种字符组合
- 密码有效期180天
- 如果不想使用这种密码复杂策略,可以使用--initialize-insecure
如果初始化报如下错误:
error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
则安装以下包即可:
[root@mysql mysql]# yum install libaio-devel
[root@mysql ~]# mkdir -p /data/mysql/data
[root@mysql ~]# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data --explicit_defaults_for_timestamp
2021-07-14T15:04:08.596953Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-07-14T15:04:08.617238Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-07-14T15:04:08.684478Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bd581429-e4b4-11eb-8cfb-000c29b39c96.
2021-07-14T15:04:08.686754Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-07-14T15:04:08.689282Z 1 [Note] A temporary password is generated for root@localhost: JY!btpV5tpt_
[root@mysql ~]# ll /data/mysql/data/
total 110628
-rw-r-----. 1 mysql mysql 56 Jul 14 23:04 auto.cnf
-rw-r-----. 1 mysql mysql 419 Jul 14 23:04 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 14 23:04 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 14 23:04 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 14 23:04 ib_logfile1
drwxr-x---. 2 mysql mysql 4096 Jul 14 23:04 mysql
drwxr-x---. 2 mysql mysql 8192 Jul 14 23:04 performance_schema
drwxr-x---. 2 mysql mysql 8192 Jul 14 23:04 sys
11.配置SSL支持
[root@db01 ~]# mysql_ssl_rsa_setup --datadir=/data/mysql/data
Generating a 2048 bit RSA private key
......................................................................+++
......+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
.............................+++
..........+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
...........................+++
...+++
writing new private key to 'client-key.pem'
-----
分配权限:
[root@db01 ~]# cd /data/mysql/data
[root@db01 data]# chown mysql:mysql *.pem
12.配置my.cnf参数文件
Mysql my.cnf:
[root@server01 mysql]# cat > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=1
port=3307
[mysql]
socket=/tmp/mysql.sock
EOF
13.配置启动文件
两种启动方式:
- sys-v
cp support-files/mysql.server /etc/init.d/mysqld
/etc/init.d/mysqld start
service mysqld restart
- systemd
cat >/etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Service]
User=mysql
Group=mysql
ExecStart= /application/mysql/bin/mysqld --defaults-file=/etc/my.cnf
ExecReload= /etc/init.d/mysqld restart
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target
EOF
1,创建启动文件 .service
vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
After=network.target
After=syslog.target
[Service]
User=mysql
Group=mysql
Type=forking
PermissionsStartOnly=true
ExecStart= /etc/init.d/mysqld start
ExecStop= /etc/init.d/mysqld stop
ExecReload= /etc/init.d/mysqld restart
LimitNOFILE = 5000
[Install]
WantedBy=multi-user.target
2,重新加载一下服务的配置文件
systemctl daemon-reload
3,开启mysql服务
systemctl start mysqld.service
systemctl start mysqld
4,关闭mysql服务
systemctl stop mysqld.service
systemctl stop mysqld
14.Mysql启动
| 启动命令 | 关闭命令 |
| -------------------------- | ----------------------------- |
| mysqld --user=mysql & | mysqladmin -uroot -p shutdown |
| mysqld_safe --user=mysql & | mysqladmin -uroot -p shutdown |
| /etc/init.d/mysql start | /etc/init.d/mysql stop |
[root@mysql ~]# mysqld_safe --user=mysql &
判断Mysql是否启动:
netstat -lnp |grep 3307
ps -ef |grep mysql
lsof -i:3307
15.创建root密码
[root@mysql ~]# mysqladmin -uroot -p password '123456'
16.删除test库相关信息
[root@mysql ~]# mysql_secure_installation
三、配置Master
1.开启二进制
[root@server01 ~]# vi /etc/my.cnf
log_bin=mysql-bin
2.配置server_id
[root@server01 ~]# vi /etc/my.cnf
server_id=1
3.开启GTID
此步不是必须,按实际环境配置
[root@server01 ~]# vi /etc/my.cnf
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
4.创建复制用户
mysql> create user repl@'192.168.1.%' identified by '123';
mysql> grant replication slave on *.* to repl@'192.168.1.%';
5.配置字符集
此步不是必须,按实际环境配置
[root@server01 ~]# vi /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
6.Master创建数据
此步不是必须,按实际环境配置
mysql> create database mydb char set utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use mydb
Database changed
mysql> create table t (id int);
Query OK, 0 rows affected (0.00 sec)
mysql> create table t1 (id int, name varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t1 values (1,'a');
Query OK, 1 row affected (0.00 sec)
7.重启主库
[root@server01 ~]# mysqladmin -uroot -p shutdown
[root@server01 ~]# mysqld_safe --user=mysql &
8.备份主库
[root@server01 ~]# mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction > /tmp/full.sql
或
[root@server01 ~]# innobackupex -uroot -p123456 /tmp/xtrabackup/full
四、配置Slave
1.安装Slave数据库
参考Master
2.开启二进制
[root@server01 ~]# vi /etc/my.cnf
log_bin=mysql-bin
3.配置server_id
[root@server01 ~]# vi /etc/my.cnf
server_id=2
4.开启GTID
此步不是必须,按实际环境配置
[root@server01 ~]# vi /etc/my.cnf
gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1
5.配置字符集
此步不是必须,按实际环境配置
[root@server01 ~]# vi /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
6.重启从库
[root@server01 ~]# mysqladmin -uroot -p shutdown
[root@server01 ~]# mysqld_safe --user=mysql &
7.主库备份还原从库
[root@server02 mysql]# scp 192.168.1.191:/tmp/full.sql /tmp/
mysql> source /tmp/full.sql
8.确认备份点
[root@server02 ~]# more /tmp/full.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=154;
9.创建同步进程
mysql> help change master to
CHANGE MASTER TO
MASTER_HOST='master2.example.com',
MASTER_USER='replication',
MASTER_PASSWORD='password',
MASTER_PORT=3306,
MASTER_LOG_FILE='master2-bin.001',
MASTER_LOG_POS=4,
MASTER_CONNECT_RETRY=10;
按实际环境修改:
CHANGE MASTER TO
MASTER_HOST='192.168.1.191',
MASTER_USER='repl',
MASTER_PASSWORD='123',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=154,
MASTER_CONNECT_RETRY=10;
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.1.191',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123',
-> MASTER_PORT=3307,
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=154,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
10.从库开启同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
11.查看主从状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.191
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000007
Read_Master_Log_Pos: 194
Relay_Log_File: server02-relay-bin.000014
Relay_Log_Pos: 407
Relay_Master_Log_File: mysql-bin.000007
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: 194
Relay_Log_Space: 704
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: 954880d4-e57a-11eb-b6a9-000c29b39c96
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: 954880d4-e57a-11eb-b6a9-000c29b39c96:1-4
Executed_Gtid_Set: 954880d4-e57a-11eb-b6a9-000c29b39c96:1-4
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)