MySQL Replication 配置

1.Master配置

打开MySQL配置文件，

[mysqld]

log-bin=mysql-bin //启用二进制日志文件，MySQL的服务器之间数据配置依赖此文件

server-id=1 //如果为0，则会拒绝Slave的连接

innodb_flush_log_at_trx_commit=1

sync_binlog=1 //以上两行是为了保证数据的一致性和持久性

#skip-networking //如果启用，这Master和Slave之间不能通信

 

2.Slave配置

[mysqld]

server-id=2

 

3.在Master

mysql>FLUSH TABLES WITH READ LOCK;//给所有表加只读锁

mysql>SHOW MASTER STATUS;

第4步中的MASTER_LOG_FILE , MASTER_LOG_POS 从此命令显示的结果中获取

 

4.Slave

 

mysql>stop slave;

mysql> CHANGE MASTER TO

           > MASTER_HOST = 'host_name',  //Master服务器的域名或IP

           > MASTER_USER = 'user_name',  //Master数据库的用户名

           > MASTER_PASSWORD = 'password', //Master数据库的密码

           > MASTER_PORT = port_num, //Master数据库的端口

           > MASTER_CONNECT_RETRY = interval,

           > MASTER_HEARTBEAT_PERIOD = interval,

           > MASTER_LOG_FILE = 'master_log_name',

           > MASTER_LOG_POS = master_log_pos,

           > RELAY_LOG_FILE = 'relay_log_name',

           > RELAY_LOG_POS = relay_log_pos,

           > MASTER_SSL = {0|1},

           > MASTER_SSL_CA = 'ca_file_name',

           > MASTER_SSL_CAPATH = 'ca_directory_name',

           > MASTER_SSL_CERT = 'cert_file_name',

           > MASTER_SSL_KEY = 'key_file_name',

           > MASTER_SSL_CIPHER = 'cipher_list',

           > MASTER_SSL_VERIFY_SERVER_CERT = {0|1},

           > IGNORE_SERVER_IDS = (server_id_list);

 

mysql>start slave;

 

5.Master

mysql>UNLOCK TABLES;//解锁