mysql主从配置查看_MySQL 主从配置记录

一、环境说明

1.Centos 6.8 x86-64

2.mysql-community-5.6.36

3.192.168.120.222(master)

4.192.168.120.232(slave)

二、yum 源配置(启用ORACLE官方yum)

[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

六、master 配置文件部分

[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

七、查看bin-log

service mysqld start #启动mysql服务

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

八、slave配置(安装和master相同)

[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

九、slave服务器上开启复制功能

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)