1.安装mysql
参考 centos7非root下安装mysql5.7.29
主机A：192.168.62.37
主机B：192.168.62.15
2.主机A 配置my.cnf，内容如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character-set-server = utf8
port = 3306
basedir=/usrdata/mysql/mysql-5.7.29
datadir=/usrdata/mysql/mysql-5.7.29/data
socket=/usrdata/mysql/mysql-5.7.29/mysql.sock
log_error=/usrdata/mysql/mysql-5.7.29/error.log
pid-file=/usrdata/mysql/mysql-5.7.29/mysql.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links = 0
#标识唯一id（必须）
server-id=1
#开启二进制日志
log-bin=mysql-bin
#不同步的数据库，可设置多个
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
#指定需要同步的数据库（和slave是相互匹配的），可以设置多个
#binlog-do-db=test
#主－主形式需要多添加的部分
log-slave-updates
#sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2
replicate-ignore-db = mysql,information_schema,performance_schema,sys
#设置存储模式不设置默认
binlog_format=MIXED
#日志清理时间
expire_logs_days=7
#日志大小
max_binlog_size=100m
#缓存大小
binlog_cache_size=4m
#最大缓存大小
max_binlog_cache_size=521m
[client]
port=3306
default-character-set=utf8
socket=/usrdata/mysql/mysql-5.7.29/mysql.sock
3.主机B 配置my.cnf，内容如下：
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
character-set-server = utf8
port = 3306
basedir=/usrdata/mysql/mysql-5.7.29
datadir=/usrdata/mysql/mysql-5.7.29/data
socket=/usrdata/mysql/mysql-5.7.29/mysql.sock
log_error=/usrdata/mysql/mysql-5.7.29/error.log
pid-file=/usrdata/mysql/mysql-5.7.29/mysql.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links = 0
#标识唯一id（必须）
server-id=2
#开启二进制日志
log-bin=mysql-bin
#不同步的数据库，可设置多个
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=mysql
binlog-ignore-db=sys
#指定需要同步的数据库（和slave是相互匹配的），可以设置多个
#binlog-do-db=test
#主－主形式需要多添加的部分
log-slave-updates
#sync_binlog = 1
auto_increment_offset = 2
auto_increment_increment = 2
replicate-ignore-db = mysql,information_schema,performance_schema,sys
#设置存储模式不设置默认
binlog_format=MIXED
#日志清理时间
expire_logs_days=7
#日志大小
max_binlog_size=100m
#缓存大小
binlog_cache_size=4m
#最大缓存大小
max_binlog_cache_size=521m
[client]
port=3306
default-character-set=utf8
socket=/usrdata/mysql/mysql-5.7.29/mysql.sock
4.重启mysql，分别创建用于同步的用户账号
1).配置MasterA主=》MasterB从
# MasterA 主机配置
mysql> grant replication slave on *.* to 'repl'@'192.168.62.15' identified by '123456';
mysql> flush privileges; #刷新权限
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000007 | 613 | | information_schema,performance_schema,mysql,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
#File、Position 在配置从机时需要用到
#MasterB 从机配置
mysql> change master to master_host='192.168.62.37',
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000007',
master_log_pos=613;
#启动slave同步进程：
mysql> start slave;
#查看slave状态：
mysql> show slave status\G;
2).配置MasterB主=》MasterA从
# MasterB主机配置
mysql> grant replication slave on *.* to 'repl'@'192.168.62.37' identified by '123456';
mysql> flush privileges; #刷新权限
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql-bin.000004 | 613 | | information_schema,performance_schema,mysql,sys | |
+------------------+----------+--------------+-------------------------------------------------+-------------------+
#File、Position 在配置从机时需要用到
#MasterA从机配置
mysql> change master to master_host='192.168.62.15',
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000004',
master_log_pos=613;
#启动slave同步进程：
mysql> start slave;
#查看slave状态：
mysql> show slave status\G;
3).配置成功后,分别查看slave状态，
#当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.62.15
Master_User: ntrepl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 613
Relay_Log_File: nt-metra-1-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema,sys
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: 613
Relay_Log_Space: 532
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: a0ce91d7-3053-11eb-95b3-fa163e443845
Master_Info_File: /usrdata/mysql/mysql-5.7.29/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:
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
5.正确关闭slave及停止mysql服务步骤
关闭MySQL从库
1. 先查看当前的主从同步状态show slave status\G;看是否双yes
2. 分别执行stop slave，停止从库线程
3. 停止从库服务mysqladmin shutdown -u用户名 -p密码
4. 查看是否还有mysql的进程ps -ef | grep mysql
5. 如果部署了多个实例，那每个实例都要按照以上步骤来操作
关闭MySQL主库
1. 停止主库服务mysqladmin shutdown -u用户名 -p密码
2. 查看是否还有mysql的进程ps -ef | grep mysql
6.正确启动mysql主从服务步骤
启动MySQL主库
1. 启动主库服务mysqladmin start -u用户名 -p密码
2. 查看mysql的进程ps -ef | grep mysql
启动MySQL从库
1. 启动从库服务mysqladmin start -u用户名 -p密码
2. 分别启动复制start slave;
3. 检查同步状态show slave status\G;是否双yes
4. 查看mysql的进程ps -ef | grep mysql
网友评论