mysql高可用一：配置mysql 5.7 replication

1. Setting up Master Server 如下图,注意红色参数部分

[root@sty mysql]# cat /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

#

innodb_buffer_pool_size = 256M

innodb_flush_log_at_trx_commit=1

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

log_bin=/var/lib/mysql/mysql-bin-log/mysql_bin

sync_binlog=1

# 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

port = 3306

server_id = 1

# 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_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

 

建立对应文件夹

mkdir /var/lib/mysql/mysql-bin-log

赋权

chown -R mysql.mysql /var/lib/mysql/mysql-bin-log/

重启服务

service mysqld restart

2. Create Replication User on Master MySQL Server

CREATE USER 'replication_user'@'%' IDENTIFIED BY 'opensourcedbms'; 

GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

3. Setting Slave Server

[root@mysqltest mysql]# cat /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
secure_file_priv=
server_id = 2

# 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_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

service mysqld restart

4. Lock master and get binary log coordinates on MySQL master server

FLUSH TABLES WITH READ LOCK;

SHOW MASTER STATUS;

5. Data snapshot with mysqldump utility on Master MySQL Server

mysqldump --all-databases --master-data > data_dump.sql

UNLOCK TABLES;

mysql -hopensourcedbms_mysql_slave -usuperadmin -p < data_dump.sql

 

6. Setup Master MySQL Server configuration on Slave server for enabling replication

mysql > CHANGE MASTER TO MASTER_HOST='opensourcedbms_mysql_master',

MASTER_USER='replication_user',

MASTER_PASSWORD='opensourcedbms',

MASTER_LOG_FILE='mysql_bin.000002',

MASTER_LOG_POS=3172;

没有设置主机名的话，MASTER_HOST设置IP也是OK的

mysql > START SLAVE;

For looking at the status of replication you can run the following statements :

On Master

SHOW MASTER STATUS;

On Slave

SHOW SLAVE STATUS;