重庆分公司
重庆分公司，新征程启航
为企业提供网站建设、域名注册、服务器等服务
MySQL主从复制配置-创新互联
配置主数据库：
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)
测试主从复制功能（省略）
网站题目：MySQL主从复制配置-创新互联
网页URL：http://cqcxhl.com/article/dhchij.html
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。
网站题目：MySQL主从复制配置-创新互联
网页URL：http://cqcxhl.com/article/dhchij.html