centos mysql5.7主从复制_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的系统权限相关表­