MySQL主从配置 – ttlsa教程系列之MySQL

描述

MySQL主从配置规范
内网环境文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

系统:CentOS-6.0-x86_64
IP主：192.168.50.183
IP从：192.168.50.184文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

MySQL安装文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

MySQL安装配置文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

这边略过....文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

主服务器配置文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

(1) 开启binlog文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

# cat /etc/my.cnf | grep log-bin
log-bin=mysql-bin文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

(2) 在主服务器上,设置一个从数据库的账户,使用REPLICATION SLAVE赋予权限,如:文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.50.%' IDENTIFIED BY '123456';文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

(3) 备份数据以及记录pos偏移量文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

mysql> flush tables with read lock;
mysqldump --all-databases >/tmp/db.sql
mysql> show master status;//查看master数据，需要开启binlog
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 | 366 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

配置从服务器文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

(1) 导入SQL文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

# scp 192.168.50.182:/tmp/db.sql /tmp/ # 准备数据库
# mysql -uroot -p123456 < /tmp/db.sql # 从主服务器上拷贝回来的db.sql文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

(2) 修改从server-id文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

# cat /etc/my.cnf | grep server-id
server-id = 30 # 与主不同即可,如果有多个从,也不能与其他从相同
# service mysqld restart文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

(3) 开启slave文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

mysql> CHANGE MASTER TO MASTER_HOST='192.168.50.182', MASTER_USER='slave',MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=366;
mysql> slave start; //启用SLAVE文章源自运维生存时间-https://www.ttlsa.com/mysql/mysql-master-salve-install/

测试主从同步

(1) 创建表 ---主

mysql> use test;
mysql> create table a(i int);
Query OK, 0 rows affected (0.06 sec)

(2) 查看表 ---从

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| a |
+----------------+
1 row in set (0.00 sec)

可以看到从库上已经出现了在主库上创建的数据库"a"