MySQL 主从配置记录

[mysql-release]
name=mysql5.6.36
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/x86_64/
enabled=1
gpgcheck=0

yum install -y mysql-community-server mysql-community-client -y

mysql_secure_installation(过程略)

service stop msyqld
cp -a  /var/lib/mysql /db

[mysqld]
default-storage-engine = innodb   #使用innodb存储引擎
innodb_file_per_table = on        #每个innodb表一个单独文件
max_connections = 4096            #用户最大的连接数
collation-server = utf8_general_ci #配置mysql
character-set-server = utf8        #配置默认字符集
datadir=/var/lib/mysql             #数据目录
socket=/var/lib/mysql/mysql.sock   #sock文件
symbolic-links=0                   #软链接（仅MyiSAM）

log-bin=master-bin                 #启用二进制日志     
log-bin-index=master-bin.index     #中继日志索引
server-id=222                      #id不要和主服务器的一样
innodb_flush_log_at_trx_commit=1   #每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，对业务两大的影响I/O
sync_binlog=1
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

service mysqld start #启动mysql服务

   grant replication slave on *.* to 'repl20'@'192.168.1.21' identified by '123456';  #授权slave

[mysqld]
default-storage-engine = innodb
innodb_file_per_table = on
max_connections = 1024
collation-server = utf8_general_ci
character-set-server = utf8
datadir=/db
socket=/db/mysql.sock
symbolic-links=0

relay-log = relay-log                       #中继日志，slave不需要配置开启bin-log
relay-log-index = relay-log.index           #中继日志索引

server-id=21                                #要唯一，一般IP地址结尾
innodb_flush_log_at_trx_commit=1
sync_binlog=1

read-only=on   　　　　　　　　　　　　　　　　　#此项比较重要slave只读模式，此项只对非管理员有效。                            

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/db/mysql.sock

mysql> change master to master_host='192.168.1.20',master_user='repl20',master_password='123456',master_log_file='master-bin.000004',master_log_pos=120124; #slave 开启复制功能

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.20
Master_User: repl20
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000004
Read_Master_Log_Pos: 120124
Relay_Log_File: relay-log.000010
Relay_Log_Pos: 120288
Relay_Master_Log_File: master-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0
Last_Error: 
Skip_Counter: 0
Exec_Master_Log_Pos: 120124
Relay_Log_Space: 120619
Until_Condition: None
Until_Log_File: 
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error: 
Last_SQL_Errno: 0
Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 20
Master_UUID: 1a5175af-345e-11e7-adab-005056ba1963
Master_Info_File: /db/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0
1 row in set (0.00 sec)