配置主数据库：
my.cnf：
server-id = 1
log-bin
重启数据库
登录并查看：
[root@Mysql-server ~]# mysql -uroot -p199429
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
建立主从复制账号：
mysql> grant replication slave on *.* to 'rep'@'192.1.1.%' identified by '199429';
mysql> select user,host from mysql.user;
+-----------+-----------+
| user | host |
+-----------+-----------+
| root | 127.0.0.1 |
| bbs | 192.1.1.% |
| keer | 192.1.1.% |
| rep | 192.1.1.% |
| wordpress | 192.1.1.% |
| root | localhost |
+-----------+-----------+
实现对主数据库锁表只读：
mysql> flush table with read lock;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |#####
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |#####
+----------------------------+----------+
10 rows in set (0.00 sec)
查看主库状态：
mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| Mysql-server-bin.000001 | 962 | | |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
新开窗口备份导出数据：
[root@Mysql-server ~]# mkdir -p /server/backup/
[root@Mysql-server ~]# mysqldump -uroot -p199429 --events -A -B |gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
在此查看主库状态是否变化：
mysql> show master status;
+-------------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+--------------+------------------+
| Mysql-server-bin.000001 | 962 | | |
+-------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从数据库：
配置文件my.cnf
server-id = 2 ####保证唯一性
重启从数据库
登录从数据库：
[root@Mysql-server_02 backup]# mysql -uroot -p199429 -S /data/3306/mysql.sock
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3 |
+---------------+-------+
恢复MySQLdump的数据
[root@Mysql-server_02 backup]# cd /server/backup/
[root@Mysql-server_02 backup]# gzip -d mysql_bak.2017-03-23.sql.gz
[root@Mysql-server_02 backup]# mysql -uroot -p'199429' -S /data/3306/mysql.sock
CHANGE MASTER TO
-> MASTER_HOST='192.1.1.11',
-> MASTER_PORT=3306,
-> MASTER_USER='rep',
-> MASTER_PASSWORD='199429',
-> MASTER_LOG_FILE='Mysql-server-bin.000001',
-> MASTER_LOG_POS=962;
费登录状态执行方法：
[root@Mysql-server_02 backup]# mysql -uroot -p'199429' -S /data/3306/mysql.sock<< EOF
CHANGE MASTER TO
MASTER_HOST='192.1.1.11',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='199429',
MASTER_LOG_FILE='Mysql-server-bin.000001',
MASTER_LOG_POS=962;
EOF
实际修改从库中的master.info文件
[root@Mysql-server_02 backup]# cat /data/3306/data/master.info
18
mysql-server-bin.000001
962
192.1.1.11
rep
199429
3306
60
0
，。。。。。。。。
启动主从复制：
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.1.1.11
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Mysql-server-bin.000001
Read_Master_Log_Pos: 962
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 260
Relay_Master_Log_File: Mysql-server-bin.000001
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: 962
Relay_Log_Space: 410
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: 1
1 row in set (0.00 sec)
测试主从复制功能（省略）
成都创新互联公司专注于尼木企业网站建设,响应式网站设计,商城网站开发。尼木网站建设公司,为尼木等地区提供建站服务。全流程按需策划设计，专业设计，全程项目跟踪，成都创新互联公司专业和态度为您提供的服务另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
本文标题：MySQL主从复制配置-创新互联
标题URL：http://bjjierui.cn/article/dhchij.html
