安装环境：
OS CentOS 7.6
Mysql 8.0.26
同步方式：基于mysql binlog机制来实现主从数据库逻辑同步。
1.Mysql 源码安装
1.1 卸载系统里面的mariadb
rpm -qa|grep mariadb
rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
1.2 安装基础软件包
yum -y install libaio ncurses-devel
1.3 解压安装包
cd /usr/local
tar zxvf /media/mysql-8.0.26-el7-x86_64.tar.gz
ln -s mysql-8.0.26-el7-x86_64 mysql
1.4 增加mysql系统用户
useradd -r -s /bin/false mysql
cd /usr/local/mysql
mkdir mysql-files run log
touch log/mysqld.log
chown -R mysql:mysql /usr/local/mysql /usr/local/mysql-8.0.26-el7-x86_64 /data
chmod 750 mysql-files
1.5 增加环境变量
vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
sed -i '$aexport PATH=$PATH:/usr/local/mysql/bin' /etc/profile
source /etc/profile
1.6 初始化mysql
bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --console --lower-case-table-names=1
记录临时密码，方便后续修改管理员密码。
使用--initialize生成随机密码，使用--initialize-insecure生成空密码.
如果，需要开启不区分表名大小写，需要在初始化时加入--lower-case-table-names=1。
其中：
--user 指定用户
--basedir 指定Mysql安装目录
--datadir 指定数据目录
--console 控制台输出初始化信息
--lower-case-table-names=1 不区分大小写（Mysql 8.0默认为区分大小写）
开启ssl连接
bin/mysql_ssl_rsa_setup --datadir=/data/mysql
1.7 创建配置文件
touch /etc/my.cnf
vim /etc/my.cnf
[client] port = 3060 default-character-set=utf8 [mysqld] default-storage-engine=innodb innodb_file_per_table=1
innodb_flush_log_at_trx_commit=1 ##事物redolog直接持久化到磁盘 max_allowed_packet=16M character_set_server=utf8 port = 3060 max_connections=2000 init_connect='SET NAMES utf8' basedir=/usr/local/mysql datadir=/data/mysql socket=/tmp/mysql.sock log-error=/usr/local/mysql/log/mysqld.log pid-file=/usr/local/mysql/run/mysqld.pid #bin log server_id=100 log-bin=/usr/local/mysql/log/product-bin binlog-ignore-db=sys,information_schema,performance_schema #binlog-do-db = disabled binlog_format=ROW binlog_row_image=FULL
sync_binlog=1 ##事物的binlog持久化到磁盘 max_binlog_size = 200m ##binlog每个日志文件大小 binlog_cache_size = 4m ##binlog缓存大小 max_binlog_cache_size = 512m ##最大binlog缓存大小 binlog_expire_logs_seconds=518400 ##binlog保留6天，默认值为30天
slave-skip-errors=all skip-slave-start=1 relay_log=mysql-relay-bin #expire_logs_days=10 ##该参数，mysql8.0版本失效 #slow query log slow_query_log=1 slow_query_log_file=/usr/local/mysql/log/slow_query.log lower_case_table_names = 1 [mysqld_safe] lower_case_table_names = 1 log-error=/usr/local/mysql/log/mysqld.log pid-file=/usr/local/mysql/run/mysqld.pid
1.8 配置Mysql开机启动
vi /etc/systemd/system/mysqld.service
[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
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
#添加到开机启动
systemctl enable mysqld.service
systemctl start mysqld
systemctl status mysqld
systemctl stop mysqld
1.9 修改Mysql管理员密码
alter user 'root'@'localhost' identified with mysql_native_password by 'Mysql#2021';
create user 'root'@'%' identified with mysql_native_password by 'Mysql#2021';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
2 配置Mysql主从同步（基于binlog）
2.1 创建用于复制操作的用户
mysql> CREATE USER 'repl'@'172.31.60.204' IDENTIFIED WITH mysql_native_password BY 'Repl#2021';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.31.60.204';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2.2 查询主节点状态
mysql> flush tables with read lock; ##锁表，生产环境中不让进新的数据，方便定位同步位置。 mysql> SHOW MASTER STATUS; +---------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+-------------------+ | binlog.000002 | 850 | | | | +---------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> unlock tables; ##解锁
2.3 从（Slave）节点上设置参数
2.3.1 my.cnf 参数修改
[mysqld]
server-id = 2 ##不能与主服务器一致
log-bin=/usr/local/mysql/log/product-bin
#replicate-do-db = disabled
replicate-ignore-db = mysql,sys,information_schema,performance_schema
2.3.2 从数据库配置
mysql> CHANGE MASTER TO
MASTER_HOST='172.31.60.203',
MASTER_USER='repl',
MASTER_PASSWORD='Repl#2021',
master_port=3060,
MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=850;
2.3.3.查看主从同步状态
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.31.60.203
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 850
Relay_Log_File: iZvy201a8evxfqvyn9fjpdZ-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000002
Slave_IO_Running: No
Slave_SQL_Running: No
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: 850
Relay_Log_Space: 156
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: NULL
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: 0
Master_UUID:
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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, 1 warning (0.01 sec)
ERROR:
No query specified
2.3.4 开启主从同步。
mysql> start slave;
再查看主从同步状态
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.31.60.203
Master_User: repl
Master_Port: 3060
Connect_Retry: 60
Master_Log_File: binlog.000002
Read_Master_Log_Pos: 850
Relay_Log_File: iZvy201a8evxfqvyn9fjpdZ-relay-bin.000002
Relay_Log_Pos: 321
Relay_Master_Log_File: binlog.000002
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: 850
Relay_Log_Space: 548
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: 851839f0-eb7c-11eb-bfaa-00163e000e71
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica 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, 1 warning (0.00 sec)
ERROR:
No query specified
查看下面两项值均为Yes，即表示设置从服务器成功。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
主从库同步延迟查看：
Seconds_Behind_Master: 0
2.3.5 设置备库为只读：
mysql>set global read_only=1;
只读状态查看：
mysql> show global variables like "%read_only%"; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_read_only | OFF | | read_only | ON | | super_read_only | OFF | | transaction_read_only | OFF | +-----------------------+-------+ 4 rows in set (0.01 sec)
从库只读状态说明：
1） read_only=1只读模式，salve仍然会读取master上的日志，并且在slave库中应用日志，保证主从数据库同步一致；
2） read_only=1只读模式，可以限定普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作；
3） 如果要限定具有super权限的用户也不能进行读写操作，就需要给所有的表加读锁的命令 “flush tables with read lock;”，不过这样的话，虽然slave仍然会从master读取到binlog日志，但是不能应用日志，与master数据不同步。
3.主备同步测试
Mysql主从同步配置完成。