一、环境准备
操作系统：CentOS Linux release 7.9.2009 （虚拟机）
数据库版本：mysql-community-server-5.7.30-1.el7.x86_64
主服务器IP：172.18.5.129
从服务器IP：172.18.5.137
从服务器IP：172.18.5.209
编辑selinux 提醒级别 【推荐】
vim /etc/selinux/config
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=permissive
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
执行reboot 重启操作系统
打开防火墙端口【推荐】
# 打开端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
执行备份
首先进行全库备份
执行指令：mysqldump -uroot -p --all-databases > sql_all_dbxxx.sql
# 备份主库
mysqldump -uroot -p --all-databases > sql_all_db_129.sql
# 备份从库
mysqldump -uroot -p --all-databases > sql_all_db_209.sql
# 备份从库
mysqldump -uroot -p --all-databases > sql_all_db_137.sql
三、修改配置：
- 主库配置
修改master配置/etc/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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#
#开启慢sql查询
slow_query_log = ON
long_query_time=1
slow_query_log_file=/var/log/mysql-slow.log
#设置server_id,具有唯一性,一般IP最后位
server-id=129
#
##开启binlog二进制日志
log-bin=mysql-bin
#
##中继日志
relay_log=mysql-relay-bin
#
## 启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_purge = ON
relay_log_recovery = ON
#
##跳dns过解析，是客户端连接服务端接速度加快
skip-name-resolve
#
##每次写入binlog的操作都写入到磁盘
sync_binlog=1
#
##最大连接数
max_connections=800
#
### 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
#
### 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
#
##二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
#
##指定不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
- 从库配置
修改slave配置/etc/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
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#开启慢sql查询
slow_query_log = ON
long_query_time=1
slow_query_log_file=/var/log/mysql-slow.log
#
#设置server_id,具有唯一性，推荐IP最后位
server-id=137
#开启binlog日志(可以不开)
log-bin=mysql-bin
#每次写入binlog的操作都写入到磁盘
sync_binlog=1
#
##中继日志
relay_log=mysql-relay-bin
#
### 设置二进制日志使用内存大小（事务）
binlog_cache_size=1M
#
### 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
#
## 启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_purge = ON
relay_log_recovery = ON
#
##跳dns过解析，是客户端连接服务端接速度加快
skip-name-resolve
#
##最大连接数
max_connections=800
#
##二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
#
##设置只读[仅从机设置]
read_only = ON
#
##使得更新的数据写进二进制日志中
log_slave_updates = 1
#
## 错误处理
slave-skip-errors=all
#
## 设置超时
slave-net-timeout=60
##指定不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
##指定不备份的数据库#replicate_ignore_db=information_schema
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
#
四、重启查看并配置
systemctl restart mysqld
systemctl status mysqld
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.30-log 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> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1、查看所有binlog日志列表 show master logs;
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
mysql>
2、查看最新一个binlog日志的编号名称，及其最后一个操作事件结束点 show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3、刷新log日志，立刻产生一个新编号的binlog日志文件，跟重启一个效果 flush logs;
4、清空所有binlog日志 reset master;[慎用]
5、日志查看，因为是二进制文件没法用vi等打开，可以用mysql的mysqlbinlog打开。
[root@itdev9903 mysql]# mysqlbinlog /var/lib/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210711 15:39:22 server id 129 end_log_pos 123 CRC32 0x9eb654f7 Start: binlog v 4, server v 5.7.30-log created 210711 15:39:22 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
KqDqYA+BAAAAdwAAAHsAAAABAAQANS43LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAqoOpgEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AfdUtp4=
'/*!*/;
# at 123
#210711 15:39:22 server id 129 end_log_pos 154 CRC32 0x83bd21da Previous-GTIDs
# [empty]
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@itdev9903 mysql]#
在master新增复制用户并授权
-
新增复制用户slave并授权
# 创建同步账号，授权网段用户 mysql>CREATE USER 'repl'@'172.18.%.%' IDENTIFIED BY 'passwd'; mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@'172.18.%.%'; #创建同步账号授权所有IP CREATE USER 'repl'@'%' IDENTIFIED BY 'passwd'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%'; #查看是否修改成功 mysql>select host,user,authentication_string from mysql.user; mysql>FLUSH PRIVILEGES; #刷新权限查看master状态binlog日志状态及序号，并记录(重要)
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # 记住文件名 mysql-bin.000007和 Position 154;在slave上添加主库信息
-
添加主库信息
# 先停止以下,避免会出错; mysql> stop slave; Query OK, 0 rows affected (0.00 sec) # 设置master服务器的一些参数 参数说明看下面的解释 mysql> change master to master_host='172.18.5.129', master_user='repl', master_password='passwd', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=154, master_connect_retry=30; Query OK, 0 rows affected, 2 warnings (0.01 sec) #说明 master_host='192.168.148.128' # Master的IP地址 master_user='slave' # 用于同步数据的用户（在Master中授权的用户） master_password='passwd' # 同步数据用户的密码 master_port=3306 # Master数据库服务的端口 master_log_file='mysql-bin.000007' #指定Slave从哪个日志文件开始读复制数据（可在Master上使用show master status查看到日志文件名） master_log_pos=154 # 从哪个POSITION号开始读 master_connect_retry=30 #当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为60秒，同步延迟调优参数。 #开启从库 mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> # 查看slave status mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.18.5.129 Master_User: repl Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: information_schema,performance_schema,mysql,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: 154 Relay_Log_Space: 527 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: 129 Master_UUID: 75b5bff3-e054-11eb-89b6-000c29a8da63 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: 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在master 上查看slave 情况
mysql> show slave hosts; +-----------+------+------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+------+-----------+--------------------------------------+ | 209 | | 3306 | 129 | 1aec00d3-dfc2-11eb-95f7-000c29417276 | | 137 | | 3306 | 129 | 40ef8be1-e059-11eb-b29a-000c297a9f0e | +-----------+------+------+-----------+--------------------------------------+ 2 rows in set (0.00 sec) mysql>测试MySQL的主从备份
测试就是在主库中进行DDL以及DML语句的执行，看从库是否会跟主库一样发生变化
-
在master创建数据
# 创建数据库 mysql> create database db_test; mysql> use db_test; mysql> create database db_test; Query OK, 1 row affected (0.00 sec) # 再其他机器查看已存在 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_test | | | mysql | | performance_schema | | sys | +--------------------+ 8 rows in set (0.00 sec) # 执行SQL CREATE TABLE tb_test ( id int(255) NOT NULL AUTO_INCREMENT, username varchar(255) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; # 执行并测试 mysql> use db_test; Database changed mysql> CREATE TABLE tb_test ( -> id int(255) NOT NULL AUTO_INCREMENT, -> username varchar(255) DEFAULT NULL, -> PRIMARY KEY (id) -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> insert into tb_test values(1,'zhangsan'); Query OK, 1 row affected (0.00 sec) # 在slave上查看数据同步情况 mysql> select * from tb_test; +----+----------+ | id | username | +----+----------+ | 1 | zhangsan | +----+----------+ 1 row in set (0.00 sec) mysql>
-
-
五、常见优化
主库配置：
# 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
# datadir=/var/lib/mysql
# 数据挂数据盘
datadir=/data/dbs/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 统一编码
character_set_server=utf8mb4
# 默认timestamp
explicit_defaults_for_timestamp=true
# 优化配置
autocommit=1
# 最大连接数
max_connections=900
# 超请求连接数堆栈的数量
# 不可超过系统值 cat /proc/sys/net/ipv4/tcp_max_syn_backlog
back_log=200
# 连接超时时间15s
connect_timeout=15
# 从连接超时时间30s
slave_net_timeout=30
# 资源等待超时时间30s
innodb_lock_wait_timeout=30
# 允许最大连接错误数
max_connect_errors=18446744073709551615
# 设置物理内存的75%
innodb_buffer_pool_size=6G
# 并行线程数量，默认为0
innodb_thread_concurrency=8
# 显示默认事务
transaction_isolation=REPEATABLE-READ
# 不反向解释域名
skip_name_resolve=1
# 独享表空间
innodb_file_per_table=1
# 单列索引长度扩大到3072字节
innodb_large_prefix=1
# 打印deadlocks日志
innodb_print_all_deadlocks=1
innodb_sort_buffer_size=16M
# 允许单个记录超过限制值
max_allowed_packet=64M
# MySQL读入缓冲区的大小
read_buffer_size=16M
# MySQL的随机读缓冲区大小
read_rnd_buffer_size=8M
# MySQL的顺序读缓冲区大小
sort_buffer_size=8M
# 开启慢查询日志
slow_query_log=1
# 超出次设定值的SQL即被记录到慢查询日志
long_query_time=6
# 指定慢查询日志位置
slow_query_log_file=/data/dbs/logs/mysql-slow.log
# 记录下没有使用索引的查询
log_queries_not_using_indexes=1
# 记录管理语句
log_slow_admin_statements=1
# 开启复制从库复制的慢查询的日志
log_slow_slave_statements=1
# 设置每分钟增长的没有使用索引查询的日志数量
log_throttle_queries_not_using_indexes=10
# 自动清除过期日志的时间
expire_logs_days=90
# 超过100行才记录慢查询
min_examined_row_limit=100
########################################
# 设置server_id,具有唯一性,一般IP最后位
server-id=194
# 开启binlog二进制日志[默认路径：/var/lib/mysql]
log-bin=/data/dbs/binlog/mysql-bin
log_bin_index=/data/dbs/binlog/mysql-bin
# 中继日志
relay_log=/data/dbs/binlog/mysql-relay-bin
# 启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_purge = ON
relay_log_recovery = ON
# 每次写入binlog的操作都写入到磁盘
sync_binlog=1
# 默认值是1GB
max_binlog_size=128M
# 设置二进制日志使用内存大小（事务）
binlog_cache_size=4M
# 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
# 指定不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
# 指定需要同步的数据库（和slave是相互匹配的）[可不设]
# binlog-do-db=test
########################################
从库设置
# 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
# datadir=/var/lib/mysql
# 数据挂数据盘
datadir=/data/dbs/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 统一编码
character_set_server=utf8mb4
# 默认timestamp
explicit_defaults_for_timestamp=true
# 优化配置
autocommit=1
# 最大连接数
max_connections=900
# 超请求连接数堆栈的数量
# 不可超过系统值 cat /proc/sys/net/ipv4/tcp_max_syn_backlog
back_log=200
# 连接超时时间15s
connect_timeout=15
# 从连接超时时间30s
slave_net_timeout=30
# 资源等待超时时间30s
innodb_lock_wait_timeout=30
# 允许最大连接错误数
max_connect_errors=18446744073709551615
# 设置物理内存的75%
innodb_buffer_pool_size=6G
# 并行线程数量，默认为0
innodb_thread_concurrency=8
# 显示默认事务
transaction_isolation=REPEATABLE-READ
# 不反向解释域名
skip_name_resolve=1
# 独享表空间
innodb_file_per_table=1
# 单列索引长度扩大到3072字节
innodb_large_prefix=1
# 打印deadlocks日志
innodb_print_all_deadlocks=1
innodb_sort_buffer_size=16M
# 允许单个记录超过限制值
max_allowed_packet=64M
# MySQL读入缓冲区的大小
read_buffer_size=16M
# MySQL的随机读缓冲区大小
read_rnd_buffer_size=8M
# MySQL的顺序读缓冲区大小
sort_buffer_size=8M
# 开启慢查询日志
slow_query_log=1
# 超出次设定值的SQL即被记录到慢查询日志
long_query_time=6
# 指定慢查询日志位置
slow_query_log_file=/data/dbs/logs/mysql-slow.log
# 记录下没有使用索引的查询
log_queries_not_using_indexes=1
# 记录管理语句
log_slow_admin_statements=1
# 开启复制从库复制的慢查询的日志
log_slow_slave_statements=1
# 设置每分钟增长的没有使用索引查询的日志数量
log_throttle_queries_not_using_indexes=10
# 自动清除过期日志的时间
expire_logs_days=90
# 超过100行才记录慢查询
min_examined_row_limit=100
########################################
# 设置server_id,具有唯一性,一般IP最后位
server-id=195
# 开启binlog二进制日志[默认路径：/var/lib/mysql]
log-bin=/data/dbs/binlog/mysql-bin
log_bin_index=/data/dbs/binlog/mysql-bin
# 中继日志
relay_log=/data/dbs/binlog/mysql-relay-bin
# 启用relaylog的自动修复功能，避免由于网络之类的外因造成日志损坏，主从停止。
relay_log_purge = ON
relay_log_recovery = ON
# 每次写入binlog的操作都写入到磁盘
sync_binlog=1
# 默认值是1GB
max_binlog_size=128M
# 设置二进制日志使用内存大小（事务）
binlog_cache_size=4M
# 设置使用的二进制日志格式（mixed,statement,row）
binlog_format=mixed
# 指定不备份的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
binlog-ignore-db=mysql
# 指定需要同步的数据库（和slave是相互匹配的）[可不设]
# binlog-do-db=test
########################################
# 设置只读[仅从机设置]
read_only = ON
# 使得更新的数据写进二进制日志中
log_slave_updates = 1
# 错误处理
slave-skip-errors=all
# 设置超时
slave-net-timeout=60
# 指定不备份的数据库
replicate_ignore_db=information_schema
replicate_ignore_db=performance_schema
replicate_ignore_db=mysql
replicate_ignore_db=sys
#主从库配置保持一致[可不设]
# replicate-do-db=test
########################################
常见操作：
# master
reset master;
show master status;
show slave hosts;
# 首次，先停止以下,避免会出错;
stop slave;
# 设置master服务器的一些参数 参数说明看下面的解释
mysql> change master to master_host='172.18.5.129', master_user='repl', master_password='password', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=154, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#开启从库
start slave;
# slaves
show slave status\G;
change master to master_host='172.18.5.129', master_user='repl', master_password='password', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=154, master_connect_retry=30;
mysql备份
#!/bin/bash
#This is mysql db_name scripts
#Date :2018-08-13
#author：Eric.Liu
database=db_name
db_addr=127.0.0.1
databak_dir=/bak/database/$database/data
logs_dir=/bak/database/$database/logs
dumpbin=/usr/local/software/mysql/bin/mysqldump
DATE=$(date +%Y%m%d)
logFile=$logs_dir/$database'_'$DATE.log
socket_dir=/var/lib/mysql/mysql.sock
#记录备份开始时间
echo " " > $logFile
echo "-----------------------------------" >> $logFile
echo "$database backup start" >> $logFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $logFile
echo "-----------------------------------" >> $logFile
#执行压缩备份命令
echo "$dumpbin -h $db_addr --socket=$socket_dir $database|gzip > $databak_dir/$database'_'$DATE.sql.gz" >> $logFile
$dumpbin -h $db_addr --socket=$socket_dir $database |gzip > $databak_dir/$database'_'$DATE.sql.gz
#记录备份结束时间
echo "-----------------------------------" >> $logFile
echo "$database backup end" >> $logFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $logFile
echo "-----------------------------------" >> $logFile
#记录清理7天前备份文件开始时间
echo "========================================" >> $logFile
echo "-----------------------------------" >> $logFile
echo "Clear the file 7 days ago start" >> $logFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $logFile
echo "-----------------------------------" >> $logFile
#清理7天前的备份文件
echo "find /bak/database/$database/data -mtime +7 -name '*.sql.gz' -exec rm -f {} \;" >> $logFile
find /bak/database/$database/data -mtime +7 -name '*.sql.gz' -exec rm -f {} \;
#记录清理7天前备份文件结束时间
echo "-----------------------------------" >> $logFile
echo "Clear the file 7 days ago end" >> $logFile
echo $(date +"%y-%m-%d %H:%M:%S") >> $logFile
echo "-----------------------------------" >> $logFile
