MySQL master-slave configuration under CentOS

One 、 Basic configuration

 On two mysql Create databases separately in （ name 、 structure 、 The codes must be consistent 

Two 、 Main server configuration

modify my.cnf file

[mysqld]
slow_query_log=ON
long_query_time=5
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
datadir=/var/lib/mysql
server-id=001        # service id Must be unique 
read-only=0
log-bin=mysql-bin
log_bin_trust_function_creators=1
transaction-isolation=READ-COMMITTED
character-set-server = utf8
server-id=001
binlog-do-db=db1  # The name of the synchronized database 
binlog-do-db=db2  # The name of the synchronized database 
expire_logs_days=10
binlog_format=MIXED
max_allowed_packet=256M
max_binlog_size=1024m
innodb_log_file_size=2GB
group_concat_max_len=1024000
lower_case_table_names=1
max_connect_errors=1000
symbolic-links=0
log-error=/var/log/mysqld.log
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 Sign in mysql Configure the account authorization to log in to the master server from the server 

-- Authorized operation  

set global validate_password_policy=0;

set global validate_password_length=1;

grant replication slave on *.* to 'root'@'%' identified by '123456';

-- Refresh the permissions 

flush privileges;

# restart mysql service

service mysqld restart

3、 ... and 、 Configure slave service area

modify my.cnf file

[mysql]
#  Set up mysql Client default character set 
default-character-set=utf8
[mysqld]
# Port number （ Can't repeat 
port=3301                       
#  Set up mysql Installation directory 
basedir=/usr/local/mysql
#  Set up mysql Database data storage directory 
datadir=/data/mysql_platform/data
# Server's id（ Can't repeat 
server-id=101
socket=/data/mysql_platform/mysql.sock
#  Error log storage directory 
log_error=/data/mysql_platform/logs/mysql.log
#  Binary log storage directory 
log_bin=/data/mysql_platform/logs/mysql-bin
# Binary file format 
binlog-format=MIXED     
#  Maximum connections allowed 
max_connections=200
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8
#  The default storage engine that will be used when creating a new table 
default-storage-engine=INNODB
# The name of the synchronized database 
replicate-do-db=db1
replicate-do-db=db2
# establish slave_master_info Table of 
master-info-repository = table  
# establish mysql.slave_relay_info Table to record the location information of synchronization 
relay-log-info-repository = table

1. restart mysql service

systemctl restart mysql_platform.service

2. Log in to the main library to view relevant information

# Sign in mysql database 
mysql -urep_db_user -p
# see master The state of 
show master status\G（ Be careful ： There's no semicolon 

3. Log in from the library and connect

# Log in from library mysql
mysql -uroot -p -S /data/mysql_platform/mysql.sock
# Connect to the main server 
change master to master_host='192.168.100.195',master_user='root',master_password='123456',master_port=3306,master_log_file='m y-logbin.000067 ',master_log_pos=970;

（ Be careful ： After disconnection ,master_log_file and master_log_pos It will change , Reconnection needs to be checked again ）
4. start-up slave

start slave

5. see slave The state of

show slave status\G

Be careful ： Only 【Slave_IO_Running】 and 【Slave_SQL_Running】 All are Yes, Then synchronization is normal . If it is No perhaps Connecting Neither. , Check the error log to find out the problem .
6、 The end! ~~ Let's have a try ~~