Mysql主从配置实例

– mysql 主服务器配置

– 1、更改配置

[mysqld]
log-bin=mysql-bin
server-id=1

– 2、创建用户

mysql > grant replication slave on *.* to 'rep'@'119.3.131.%' identified by '123456';

– 3、获取二进制日志的信息并导出数据库

mysql > flush tables with read lock;
mysql > show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      446 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
mysql > unlock tables;

– mysql 从服务器配置
– 1、更改配置

[mysqld]
server-id=2

– 2、设置主从关系 登录mysql

mysql> CHANGE MASTER TO MASTER_HOST='master_host_name',MASTER_PORT='master_host_port',MASTER_USER='replication_user_name',MASTER_PASSWORD='replication_password',MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS='recorded_log_file_pos';
mysql> CHANGE MASTER TO MASTER_HOST='47.101.37.28',MASTER_PORT='3306',MASTER_USER='rep',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=446;
mysql > start slave;
mysql > show slave status \G           -- 查看Slave_IO_Running和Slave_SQL_Running是不是Yes