MySQL 5.7 线下部署
主从安装mysql：
yum install https://home.vimll.com:9999/download/mysql/mysql57-community-release-el7-10.noarch.rpm
yum install mysql-community-server
本地文件安装
wget https://home.vimll.com:9999/download/mysql/mysql-57-42.zip
unzip mysql-57-42.zip
cd mysql-57-42;yum install * -y
cat > /etc/my.cnf << EOF
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[client]
port = 3306
default-character-set = utf8mb4
[mysql]
port = 3306
default-character-set = utf8mb4
[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=/opt/mysql/data
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
skip-name-resolve
lower_case_table_names=1
max_connections = 1000
max_connect_errors = 10000
max_user_connections = 900
innodb_buffer_pool_size = 72G # 视资源情更改。物理内存的一半即可
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id = 100
log_bin = mysql-bin
binlog_format = MIXED
#sync_binlog = 1
expire_logs_days = 30
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
default-time-zone='+08:00'
default_storage_engine=InnoDB
slow_query_log = ON
slow_query_log_file = /opt/mysql/data/localhost-slow.log
long_query_time = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#主从同步配置
#binlog-do-db = test #要同步的数据库
#binlog-ignore-db = mysql #不需要同步的数据库
#binlog_ignore_db = information_schema
#binlog_ignore_db = performation_schema
#binlog_ignore_db = sys
[mysqldump]
quick
max_allowed_packet = 128M
user=dumper
password=Dumper@123
EOF
cat /var/log/mysqld.log |grep pass
mysql -uroot -p
alter user 'root'@'localhost' identified by 'xxxxx';
create database buswash;
create user buswash@'%' identified by 'xxxxxx';
GRANT all privileges ON buswash.* TO `buswash`@`%` ;
create database test;
create user test@'%' identified by 'Test123';
GRANT all privileges ON test.* TO `test`@`%` ;
0 2 * * * mysqldump --databases buswash |gzip > /backup/mysql-backups/buswash_`date +\%F`.sql.gz
0 4 * * * find /backup/mysql-backups/ -type f -mtime +15 |xargs rm -f
#主从配置
主库创建同步用户：
CREATE USER 'slave'@'%' IDENTIFIED BY 'Slave@123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
log_bin是否开启
mysql> show variables like 'log_bin';
从库配置差异点：
cat /etc/my.cnf
......
server-id = 210 #必须唯一
log_bin = mysql-bin #开启及设置二进制日志文件名称
binlog_format = MIXED
sync_binlog = 1
expire_logs_days =7 #二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
log_slave_updates = 1 #【关键点】从主服务器接收到的更新同时要写入二进制日志
......
登录从库：
stop slave;
CHANGE MASTER TO
MASTER_HOST='192.168.9.20',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='Slave123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
start slave;
stop slave; //停止同步
start slave; //开始同步
//【关键处】从主库的该log_bin文件开始读取同步信息，主库show master status返回结果
MASTER_LOG_FILE='mysql-bin.000001',
//【关键处】从文件中指定位置开始读取，主库show master status返回结果
MASTER_LOG_POS=154;
查看同步状态：
show slave status \G
Slave_IO_Running和Slave_SQL_Running都是Yes，则同步是正常的。
正常主从切换：
为了在切换时不会漏掉数据库的更新，必须将主数据库停掉，设为只读：set global read_only=1;调整完后 set global read_only=0;
主库暂停后从库检查 确保从服务器已经处理了日志中的所有语句
STOP SLAVE IO_THREAD；
SHOW PROCESSLIST；
Has read all relaylogwaiting for the slave I/O thread to update it
从库暂停同步
STOP SLAVE；
RESET MASTER；
RESET SLAVE；
重启
创建同步用户
CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
Show master status \G;
原主库 RESET MASTER；并接入新主库同步
stop slave;
CHANGE MASTER TO
MASTER_HOST='192.168.9.20',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
start slave;
show slave status \G;
Show master status \G;
主库宕机切换：
--查询从库状态
show slave status\G
--查询主库状态
show slave hosts;
--从库: 停止 IO_THREAD 线程
stop slave IO_THREAD;
show slave status\G
SHOW PROCESSLIST；
Has read all relaylogwaiting for the slave I/O thread to update it
--激活从库(从库上操作)
stop slave;
reset master;
reset slave all;
show binary logs;
备注：reset slave all 命令会删除从库的 replication 参数，之后 show slave status\G 的信息返回为空。
Show master status \G;
--将原来主库变为从库
stop slave;
CHANGE MASTER TO
MASTER_HOST='192.168.9.20',
MASTER_PORT=3306,
MASTER_USER='slave',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=154;
start slave;
VIM Linux Life