mysql运维主从_MySQL主从配置

MySQL主从配置

描述

MySQL主从配置规范

内网环境

系统:CentOS-6.0-x86_64

IP主：192.168.50.183

IP从：192.168.50.184

MySQL安装

MySQL安装配置

这边略过....

主服务器配置

(1) 开启binlog

#cat /etc/my.cnf | grep log-bin

log-bin=mysql-bin

(2) 在主服务器上,设置一个从数据库的账户,使用REPLICATION SLAVE赋予权限,如:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.50.%' IDENTIFIED BY '123456';

(3) 备份数据以及记录pos偏移量

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)

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

配置从服务器

(1) 导入SQL

# scp 192.168.50.182:/tmp/db.sql /tmp/ # 准备数据库

# mysql -uroot -p123456 < /tmp/db.sql # 从主服务器上拷贝回来的db.sql

(2) 修改从server-id

# cat /etc/my.cnf | grep server-id

server-id = 30 # 与主不同即可,如果有多个从,也不能与其他从相同

# service mysqld restart

(3) 开启slave

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

测试主从同步

(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"