MySQL配置主从复制

主数据库 10.200.11.224 从数据库 10.200.11.230 1、在主数据库添加用户 添加用户 GRANT REPLICATION SLAVE ON *.* TO ‘slave_test’@’10.200.11.%’ IDENTIFIED BY ‘123456’; #添加用户slave_test并授权 查看是否添加成功 使用该账号在从库机器上登录一次试试 mysql -h 10.200.11.224 -u slave_test -p 如果不能登录需要将/etc/mysql/my.cnf当中bind-address= 127.0.0.1行屏蔽 2、 修改主数据库配置/etc/mysql/my.cnf(修改之前记得备份) server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #配置数据库复制 binlog_do_db = test_a binlog_do_db = test_b #配置数据库不做复制 binlog_ignore_db = information_schema binlog_ignore_db = mysql binlog_ignore_db = performance_schema

3、查看主数据库状态 mysql>show master status \G; File: mysql-bin.000001 Position: 107 Binlog_Do_DB: test_a,test_b Binlog_Ignore_DB: information_schema,mysql,performance_schema

4、修改从库配置/etc/mysql/my.cnf server-id = 2 replicate-do-db = test_a replicate-do-db = test_b replicate-ignore-db = mysql replicate-ignore-db = information_schema replicate-ignore-db = performance_schema #5.1.7之后要在MYSQL命令行执行 change master to master_host=’10.200.11.224′, master_user=’slave_test’, master_password=’123456′,master_port=3306,master_log_file=’mysql-bin.0000011′,master_log_pos=107; stop slave; reset slave; start slave;

备注： 主服务器查看 show processlist; show master status \G; 从服务器查看 start slave; show slave status \G; Slave_IO_Running: Yes Slave_SQL_Running: Yes 说明主从已正常工作了