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主从复制配置-创新互联
地址分享:http://myzitong.com/article/dhchij.html
另外有需要云服务器可以了解下创新互联cdcxhl.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
分享名称:MySQL主从复制配置-创新互联
地址分享:http://myzitong.com/article/dhchij.html