MySQL主从配置192.168.12.24操作：create database mxyc;创建数据库insert into mysql.user(Host,User,Password) values('localhost','mxyc',password('123qwe'));建立账号密码grant all on mxyc.* to 'mxyc'@'192.168.12.25' identified by '123qwe' with grant option;授权用户mxyc从192.168.12.25（25是web服务器，因为我这个没有web，就写的是从服务器）访问数据库insert into mysql.user(Host,User,Password) values('localhost','mxycdbbak',password('123qwe'));建立mysql主从数据库同步用户mxycdbbakflush privileges;刷新系统授权表grant replication slave on *.* to 'mxycdbbak'@'192.168.12.25' identified by '123qwe' with grant option 授权用户只能从192.168.12.25访问数据库mysqldump -uroot -p123qwe --default-character-set=utf8 --opt -Q -R --skip-lock-tables mxyc > /cacti/mxyc.sql导出数据库
192.168.12.25操作：mysql -uroot -p123qwecreate database mxyc;use mxycsource /cacti/mxyc.sql
192.168.12.24操作：vi /etc/my.cnf[mysqld]server-id=1log-bin=mysql-binbinlog-do-db=mxycbinlog-ignore-db=mysql
/etc/init.d/mysqld restart重启mysqlmysql -uroot -p123qwe进入mysqlshow variables like 'server_id';查看serverID是不是1show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000001 | 107 | mxyc | mysql |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)记录file的值： mysql-bin.000001和position的值107，后面会用到
192.168.12.25操作：vi /etc/my.cnf[mysqld]server-id=2 设置服务器id，修改其值为2，表示为从数据库log-bin=mysql-bin 启动MySQ二进制日志系统，注意：如果原来的配置文件中已经有这一行，就不用再添加了binlog-do-db=mxyc 需要同步的数据库名，如果有多个数据库，可重复此参数，每个数据库一行binlog-ignore-db=mysql 不同步mysql系统数据库read_only 设置数据库只读
mysql -uroot -p123qweshow variables like 'server_id'; 查看server-id的值，必须为上面设置的2，否则请返回修改配置文件stop slave;change master to master_host='192.168.12.24',master_user='mxycdbbak',master_password='123qwe',master_log_file='mysql-bin.000001' ,master_log_pos=107;slave start;SHOW SLAVE STATUS\G查看slave同步信息，出现以下内容注意：Slave_IO_Running: YesSlave_SQL_Running: Yes以上两个参数的值为yes，即说明配置成功
测试：192.168.12.24操作：mysql -uroot -p123qweuse mxyc 连接数据库CREATE TABLE test ( id int not null primary key,name char(20) );创建表show tables;+----------------+| Tables_in_mxyc |+----------------+| test |+----------------+1 row in set (0.00 sec)
192.168.12.25操作：mysql -uroot -p123qweuse mxycshow tables; 查看表+----------------+| Tables_in_mxyc |+----------------+| test |+----------------+1 row in set (0.00 sec)
转载于:https://www.cnblogs.com/zclzhao/p/4917339.html
