MySQL安装以及配置主从复制

scp localFile root@192.168.0.106:/usr/

vi /etc/hosts

192.168.253.100  db1
192.168.253.101  db2
192.168.253.102  db3
//换成自己对应的各个ip

systemctl stop firewalld        		#停止firewall
systemctl disable firewalld			#禁止firewall开机启动

setenforce 0
sed -i 's/SELINUX=.*/SELINUX=disabled/' /etc/selinux/config

vi /etc/security/limits.conf
mysql        soft  nproc      65535
mysql        hard  nproc      65535
mysql        soft  nofile     65535
mysql        hard  nofile     65535

useradd mysql 
passwd mysql				#密码自行配置，这里用的 password

mkdir -p  /data/{log,data,tmp,binlog,relaylog}

rpm -qa|grep mariadb
rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps

tar -xzvf /root/mysql-5.7.32-el7-x86_64.tar.gz -C /
mv /mysql-5.7.32-el7-x86_64 /mysql
chown -R mysql:mysql /mysql /data	     #递归改变文件用户所有权

cp /mysql/support-files/mysql.server /etc/init.d/mysqld
lfconfig						
echo "PATH=$PATH:/mysql/bin" > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
chkconfig mysqld on

vi /etc/my.cnf
#（master和slave只有server_id不同）

[client]
socket=/mysql/mysql.sock
[mysqld] 
port=3306
user=mysql
basedir=/mysql #mysql路径
socket=/mysql/mysql.sock
#加载半同步复制主备插件
plugin-load-add=semisync_master.so
plugin-load-add=semisync_slave.so
symbolic-links=0
log-error=/data/log/mysqld.log
slow_query_log_file=/data/log/slow.log
slow_query_log=1
long_query_time=0.3
#server_id三台分别是1,2,3
server_id=1
#开启gtid模式
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=1
binlog_checksum=NONE
log_slave_updates=ON
log_bin=/data/binlog/binlog
relay_log=/data/relaylog/relaylog
binlog_format=ROW
transaction_write_set_extraction=XXHASH64
datadir=/data/data
slave_parallel_type=LOGICAL_CLOCK
slave_preserve_commit_order=1
slave_parallel_workers =4  
innodb_file_per_table
sync_binlog = 1
binlog-group-commit-sync-delay=20  
binlog_group_commit_sync_no_delay_count=5  
innodb_lock_wait_timeout = 50
innodb_rollback_on_timeout = ON
innodb_io_capacity = 5000  
innodb_io_capacity_max=15000
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 200
innodb_spin_wait_delay = 6
innodb_status_file = 1
innodb_purge_threads=4
innodb_undo_log_truncate=1  
innodb_max_undo_log_size=4G
innodb_use_native_aio = 1
innodb_autoinc_lock_mode = 2
log_slow_admin_statements=1
expire_logs_days=7  
character-set-server=utf8mb4
collation-server= utf8mb4_bin
skip-name-resolve
lower_case_table_names
skip-external-locking
max_allowed_packet = 1024M
table_open_cache = 4000
table_open_cache_instances=16
max_connections = 4000
query_cache_size = 0
query_cache_type = 0
tmp_table_size = 1024M
max_heap_table_size = 1024M
innodb_log_files_in_group = 3
innodb_log_file_size = 1024M
innodb_flush_method= O_DIRECT
log_timestamps=SYSTEM
#三个节点auto_increment_offset的值可以跟server_id相同
auto_increment_offset=1
auto_increment_increment=6  
explicit_defaults_for_timestamp
log_bin_trust_function_creators = 1
transaction-isolation = READ-COMMITTED
innodb_buffer_pool_instances=8
innodb_write_io_threads=4  
innodb_read_io_threads=4 
#生产环境增加
innodb_buffer_pool_size=2G  
innodb_flush_log_at_trx_commit=1
#设置从节点应用relaylog后产生binlog
log_slave_updates=1
#设置不自动删除relaylog
#relay_log_purge=0
#设置增强半同步复制参数
rpl_semi_sync_master_wait_point= AFTER_SYNC
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
rpl_semi_sync_slave_enabled=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

/mysql/bin/mysqld --user=mysql --basedir=/mysql --datadir=/data/data --initialize

启动前先创建mysql日志文件
su - mysql
touch /data/log/mysqld.log
exit

systemctl start mysqld
#确保启动没报错，ps -ef|grep mysql 可以看到进程

#（主从节点均需执行,注意root密码）
root初始密码查找： grep password  /data/log/mysqld.log 
mysql -uroot -pR00T_12344
mysql>SET SQL_LOG_BIN=0;
set password='R00T_12344'; 
FLUSH PRIVILEGES;
use mysql;
update user set host = '%' where user = 'R00T_12344';
flush privileges;
SET SQL_LOG_BIN=1;

#select user,host from mysql.user 可以查看当前已开放
#如查询后root没有开放权限，尝试使用  
GRANT ALL PRIVILEGES ON * . * TO 'root'@'%' IDENTIFIED BY 'R00T_12344' WITH GRANT OPTION;
flush privileges;

mysql>change master to master_host='db1',master_port=3306,master_user='root',master_password='R00T_12344',MASTER_AUTO_POSITION=1;	 

#因为开启了gtid，可以设置MASTER_AUTO_POSITION=1使主从复制自动按照gtid的位置复制

#启动slave
mysql>START SLAVE; 
#查看从库的状态
mysql>SHOW SLAVE STATUS\G;

#确保 ：
#Slave_IO_Running: Yes 
#Slave_SQL_Running: Yes 

set global super_read_only=1;

mysql>
create database test;
use test;
create table test (id int primary key);
insert into test values(1);

#从节点db2：（新主节点）
mysql>
stop slave;
set global read_only=0;

#原主节点db1：
mysql>
set global super_read_only=1;
change master to master_host='db2',master_port=3306,master_user='rpl_user',master_password='R00T_12344',MASTER_AUTO_POSITION=1;
start slave;
show slave status \G;

#从节点db3：
mysql>
show slave status \G;
stop slave;
change master to master_host='db2',master_port=3306,master_user='rpl_user',master_password='R00T_12344',MASTER_AUTO_POSITION=1;
start slave;
show slave status \G;

#db2上插入数据，检查db1，db3是否数据同步