CentOS 7 搭建MySQL5.7 主从复制
推荐使用二进制安装方式，配置较为灵活
一、安装
1.下载安装包
官网地址：www.mysql.com
2.解压二进制安装包
tar -zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz
3.复制到指定目录中
cp -rf mysql-5.7.29-linux-glibc2.12-x86_64 /usr/local/mysql
4.创建MySQL用户
useradd mysql
二、配置mysql
1.创建相关目录（所有节点）
mkdir -pv /home/data/mysql57/data
mkdir -pv /home/data/mysql57/log
mkdir -pv /home/data/mysql57/run
mkdir -pv /home/data/mysql57/tmp
mkdir -pv /home/data/mysql57/relay
mkdir -pv /home/data/mysql57/binlog
2.配置文件
vim my.cnf
[mysqld]
datadir=/home/data/mysql57/data
socket=/home/data/mysql57/run/mysql.sock
tmpdir=/home/data/mysql57/tmp
user=mysql
character_set_server=utf8mb4
default_password_lifetime=0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
read_buffer_size=128M
read_rnd_buffer_size=128M
sort_buffer_size=128M
innodb_log_buffer_size=128M
key_buffer_size=128M
max_connections = 1024
query_cache_size = 512M
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
binlog-format=ROW
log-slave-updates=true
gtid-mode=on # GTID only
enforce-gtid-consistency=true # GTID only
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
port=3306 #mysql的端口
log-bin=/home/data/mysql57/binlog/mysql-bin.log
report-host=192.168.1.12 #主库的ip
server-id = 243
binlog-ignore-db=mysql
replicate-ignore-db=mysql
sync_binlog=1 #主库设置成1 ，从库设置成0
relay-log =/home/data/mysql57/relay/relay-bin
relay-log-index =/home/data/mysql57/relay/relay-bin.index
relay-log-info-file =/home/data/mysql57/relay/relay-bin.info
relay_log_recovery= 1
expire_logs_days = 10
slow_query_log_file=/home/data/mysql57/log/query-slow.log
long_query_time=1
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_log_buffer_size = 512M
innodb_buffer_pool_size = 170G #服务器内存的70%
innodb_flush_log_at_trx_commit=1 #主库设置成1，从库设置成2
innodb_lock_wait_timeout=50
key_buffer_size = 512M
skip-name-resolve
#slave-skip-errors=all
[mysqld_safe]
log-error=/home/data/mysql57/log/mysqld.log
pid-file=/home/data/mysql57/run/mysqld.pid
[client]
socket=/home/data/mysql57/run/mysql.sock
3.数据库初始化
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --basedir=/usr/local/mysql/ --datadir=/home/data/mysql57/data/ --user=mysql
注：initialize-insecure参数是生成空密码
可以忽略
4.启动
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --datadir=/home/data/mysql57/data/ --user=mysql &
注：
启动时Centos 里MySQL抛出mysqld_safe error: log-error set to '/data/mysql57/log/mysqld.log', however file don't exists. Create writable for user 'mysql'.
解决办法：echo "" > /data/mysql57/log/mysqld.log 或 touch /data/mysql57/log/mysqld.log
5.设置环境变量
vi /etc/profile
export PATH=$PATH:/usr/local/mysql/bin
6.设置MySQL开机启动
在/etc/rc.local里面添加启动命令，就可以实现MYSQL开机启动
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --datadir=/home/data/mysql57/data/ --user=mysql &
三、主从部署
1.在主库上创建复制用户
grant replication slave,replication client on *.* to ‘Repl’@’%’ identified by ‘Repl#1234’;
2.在从库执行
change master to mastet_host=’主库的ip’,master_user=’Repl’,master_password=’Repl#1234’,master_port=3306,master_auto_position=1;
3.从库执行
start slave;
show slave status\G;
注：可以通过 Seconds_Behind_Master: 查看主从延迟
4.日志中时间默认为减8小时，如果想修改为系统时间，需要修改以下参数
将配置文件中log_timestamps值 改成 system
5.设置表名不区分大小写
mysql> show variables like '%case%';
两边配置文件[mysqld]中添加lower_case_table_names=1
分别重启主从服务
四、配置
1.查看密码
mysql> select Host,User,authentication_string from mysql.user;
2.修改密码
mysql>update mysql.user set authentication_string=password('abc123') where user='root';
注：首次初始化启动后root用户一般采用空密码
/usr/local/mysql/mysql -uroot 即可登录进去
3.创建用户
语法：CREATE USER 'username'@'host' IDENTIFIED BY 'password';
username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
例子：
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'test'@'172.29.1.101_' IDENDIFIED BY '123456';
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
CREATE USER 'test'@'%' IDENTIFIED BY '';
CREATE USER 'test'@'%';
4.删除用户
drop user 'test'@'localhost';
5.用户授权
命令:GRANT privileges ON databasename.tablename TO 'username'@'host'
参数说明：
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
例子:
GRANT SELECT, INSERT ON test.user TO 'test1'@'%';
GRANT ALL ON *.* TO 'test1'@'%';
GRANT ALL ON maindataplus.* TO 'test1'@'%';
注意:
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
flush privileges; //刷新MySQL的系统权限相关表
