Mysql主从配置
系统环境：
Centos6.5
Mysql5.6
IP：1.1.1.1 主
1.1.1.2 从
操作之前先将iptables以及selinux关闭，方便测试，后期开放相应端口即可
1.修改配置文件,开启binlog以及指定server ID(两个server ID不能一样)
修改主库，1.1.1.1
vim /etc/my.cnf
server-id=1
log-bin=mysql-bin
/etc/init.d/mysqld restart
修改从库，1.1.1.2
vim /etc/my.cnf
server-id=2
log-bin=mysql-bin
/etc/init.d/mysqld restart
2.给两个数据库设置密码（初始没有密码，直接执行mysql命令登录）
use mysql
update user set password=password('666666') where user='root';
3.同步数据(第一次需要将主数据库中的数据手动同步到从数据库)
主库操作，1.1.1.1
mysqldump -uroot -p666666 --all-databases > /root/mysql.dump
scp /root/mysql.dump 1.1.1.2:/root/
从库操作，1.1.1.2
mysql -uroot -p666666
source /root/mysql.dump
4.配置主从
主库操作，1.1.1.1
mysql -uroot -p666666
grant replication slave on *.* to 'replication'@'1.1.1.2' identified by 'replication';
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000011 | 22939 | | | |
+------------------+----------+--------------+------------------+-------------------+
从库操作，1.1.1.2
mysql -uroot -p666666
mysql> change master to
-> master_host='1.1.1.1',
-> master_user='replication',
-> master_password='replication',
-> master_log_file='mysql-bin.000011',
-> master_log_pos=22939;
启动
start slave;
查看配置是否正常
mysql -uroot -p666666 -e 'show slave status\G' |grep 'Running'
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
5.测试主从同步是否正确
主库操作，1.1.1.1
mysql -uroot -p666666
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cmdb |
| luanma |
| mysql |
| performance_schema |
| t2 |
| test |
| yhl |
+--------------------+
create database zc_test charset utf8;
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cmdb |
| luanma |
| mysql |
| performance_schema |
| t2 |
| test |
| yhl |
| zc_test |
+--------------------+
从库操作，1.1.1.2
show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cmdb |
| luanma |
| mysql |
| performance_schema |
| t2 |
| test |
| yhl |
| zc_test |
+--------------------+
主库操作，1.1.1.1
use zc_test;
创建一个表
create table yhl(id int,name varchar(20),password varchar(50)) charset utf8;
插入数据
insert into yhl value(1,'yhl',123456);
查看
select * from yhl;
+------+------+----------+
| id | name | password |
+------+------+----------+
| 1 | yhl | 123456 |
+------+------+----------+
从库操作，1.1.1.2
use zc_test;
select * from yhl;
+------+------+----------+
| id | name | password |
+------+------+----------+
| 1 | yhl | 123456 |
+------+------+----------+
主库操作，1.1.1.1
drop table yhl;
select * from yhl;
ERROR 1146 (42S02): Table 'zc_test.yhl' doesn't exist
从库操作，1.1.1.2
select * from yhl;
ERROR 1146 (42S02): Table 'zc_test.yhl' doesn't exist
至此，说明配置正常
