MySQL 主从同步设置

柔光的暖阳◎ 2022-08-21 15:56 221阅读 0赞

一、设置主库

1、修改主库my.cnf,主要是设置个不一样的id和logbin

\#vim /usr/local/webserver/mysql/my.cnf

server-id = 1

log-bin= binlog

binlog-do-db=iea

2、启动主库生效

\#/bin/sh /usr/local/webserver/mysql/bin/mysqld\_safe --defaults-file=/usr/local/webserver/mysql/my.cnf &

3、登陆主库

\#/usr/local/webserver/bin/mysql -u root -p

4、赋予从库权限帐号，允许用户在主库上读取日志

mysql> grant all privileges on \*.\* to 'admin'@'%' identified by '12345678';

5、检查创建是否成功

select user,host from mysql.user;

6、锁主库表

mysql> flush tables with read lock;

7、显示主库信息

记录File和Position，从库设置将会用到

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000003 1635708   |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

8、另开一个终端登陆124，打包主库迁移数据

\# cd /usr/local/webserver/mysql/

\# tar zcvf data124.tar.gz ./data

二、设置从库

1、传输拿到主库包、解包

\#cd /usr/local/webserver/mysql/

\#tar zxvf data124.tar.gz

2、解锁主库表

mysql> unlock tables;

3、修改从库my.cnf

\# vi my.cnf

\# slave

server-id=2

master-host=192.168.4.191

master-user=admin

master-password=12345678

4、验证连接主库

\# /usr/local/webserver/mysql/bin/mysql -h 192.168.4.191 -u admin -p

5、在从库上设置同步

设置连接MASTER MASTER\_LOG\_FILE为主库的File，MASTER\_LOG\_POS为主库的Position

mysql> slave stop;

mysql> change master to master\_host='192.168.4.191',master\_user='admin', master\_password='12345678',

> master\_log\_file='binlog.000003', master\_log\_pos=1635708;

mysql> slave start;

6、启动从库服务

mysql> slave start;

7、进行测试

在主库上的iea表上建立名为yuhongchun的表

mysql> CREATE TABLE \`yuhongchun\` (    \`id\` INT( 5 ) UNSIGNED NOT NULL AUTO\_INCREMENT ,    \`username\` VARCHAR( 20 ) NOT NULL ,    \`password\` CHAR( 32 ) NOT NULL ,    \`time\` DATETIME NOT NULL ,    \`number\` FLOAT( 10 ) NOT NULL ,    \`content\` TEXT NOT NULL ,    PRIMARY KEY ( \`id\` )     ) ENGINE = MYISAM ;

在从表中马上看到了效果，主从同步成功了;为了更进一步验证在从库上输入show slave status\\G;

mysql> show slave status\\G;

Slave\_IO\_Running: Yes(网络正常);Slave\_SQL\_Running: Yes(表结构正常)，进一步验证了以上过程的正确性，截图如下(一定要保证这二项参数为YES)

在主MySQL上可输入mysql> show full processlist;观察其状态，正确结果也应该如截图所示：

![1_101840_1.jpg][]

三、如果主MySQL发生错误，如何做主从切换呢?

1、保证所有从数据库都已经执行了relay log中的全部更新，在从服务器中执行stop slave io\_thread,用show processlist检查，查看状态是否是Has read all relay log,表示更新完成.

mysql> stop slave io\_thread;

Query OK, 0 rows affected (0.01 sec)

mysql> show processlist;

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+

2 rows in set (0.00 sec)

2、在从服务器上执行stop slave，reset master命令，重置成主数据库

mysql>stop slave;

Query OK,0 affected (0.00 sec)

mysql>reset master;

Query OK,0 affected (0.00 sec)

3、删除新的主服务器数据库目录中的master.info和relay-log.info文件，否则下次重启时还会按照从服务器来启动.

MySQL主从架构投入生产前后应该注意的事项：

一、配置前，master和slave的hostname一定要取个不同的，免得配置时发生问题;另外，强烈建议ntpdate二台服务器的时间，不然来个未来(feature)时间就麻烦了。

二、由于MySQL数据库走的都是内网，所以二台机器的iptables可以关闭，在配置过程中由于没关iptables发生了错误，直接导致admin在slave数据库上连不上主数据库，这个特指出来给大家借荐 下;

三、主MySQL的binlog功能一定要打开，我们的线上服务器有次由于PHP程序误操作，发生了改单错误，幸亏用binlog恢复过来了;但开启此功能要注意binlog的大小，有次Nagios狂报警，binlog日志都快1T了;

四、如果slave服务器同步时出现以下报错：

The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it)

说明方从服务器里my.cnf中的server-id有相同的。解决办法:修改my.cnf里的server-id，并重启数据库服务。

五、本着防患于未然，如果做主MySQL的备份时，请尽快用shell脚本同时做下FTP的备份工作，即将本地备份数据即时FTP到存储服务器上，事实证明：这样能将备份是救命的稻草的宗旨执行得更为彻底。

[1_101840_1.jpg]: http://www.php100.com/cms/uploads/101103/1_101840_1.jpg