mysql主从最小代价配置_MySQL主从配置 - ttlsa教程系列之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"