mysql主从复制

最新推荐文章于 2025-07-09 21:23:52 发布

转载最新推荐文章于 2025-07-09 21:23:52 发布 · 421 阅读

mysql 专栏收录该内容

44 篇文章

订阅专栏

mysql主从复制
（超简单）

怎么安装mysql数据库，这里不说了，只说它的主从复制，步骤如下：

1、主从服务器分别作以下操作：
1.1、版本一致
1.2、初始化表，并在后台启动mysql
1.3、修改root的密码

2、修改主服务器master:
   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=222       //[必须]服务器唯一ID，默认是1，一般取IP最后一段

3、修改从服务器slave:
   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=226       //[必须]服务器唯一ID，默认是1，一般取IP最后一段

4、重启两台服务器的mysql
   /etc/init.d/mysql restart

5、在主服务器上建立帐户并授权slave:
   #/usr/local/mysql/bin/mysql -uroot -pmttang
   mysql>GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'q123456'; //一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全。

6、登录主服务器的mysql，查询master的状态
   mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
   注：执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

7、配置从服务器Slave：
   mysql>change master to aster_host='192.168.145.222',master_user='tb',master_password='q123456',
         master_log_file='mysql-bin.,000004',master_log_pos=308;   //注意不要断开，“308”无单引号。

   Mysql>start slave;    //启动从服务器复制功能

8、检查从服务器复制功能状态：

   mysql> show slave status\G

   *************************** 1. row ***************************

                Slave_IO_State: Waiting for master to send event

                   Master_Host: 192.168.2.222 //主服务器地址

                   Master_User: myrync         //授权帐户名，尽量避免使用root

                   Master_Port: 3306           //数据库端口，部分版本没有此行

                 Connect_Retry: 60

               Master_Log_File: mysql-bin.000004

           Read_Master_Log_Pos: 600        //#同步读取二进制日志的位置，大于等于>=Exec_Master_Log_Pos

                Relay_Log_File: ddte-relay-bin.000003

                 Relay_Log_Pos: 251

         Relay_Master_Log_File: mysql-bin.000004

              Slave_IO_Running: Yes       //此状态必须YES

             Slave_SQL_Running: Yes       //此状态必须YES
                    ......

注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)。

以上操作过程，主从服务器配置完成。

9、主从服务器测试：

主服务器Mysql，建立数据库，并在这个库中建表插入一条数据：

mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)

mysql> use hi_db;
Database changed

mysql> create table hi_tb(id int(3),name char(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into hi_tb values(001,'bobu');
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | hi_db              |
   | mysql              |
   | test               |
   +--------------------+
   4 rows in set (0.00 sec)

从服务器Mysql查询：

   mysql> show databases;

   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | hi_db              |          //I'M here，大家看到了吧
   | mysql              |
   | test               |
   +--------------------+
   4 rows in set (0.00 sec)

   mysql> use hi_db
   Database changed
   mysql> select * from hi_tb;    //可以看到在主服务器上新增的具体数据
   +------+------+
   | id   | name |
   +------+------+
   |    1 | bobu |
   +------+------+
   1 row in set (0.00 sec)

10、完成：

编写一shell脚本，用nagios监控slave的两个“yes”，如发现只有一个或零个“yes”，就表明主从有问题了，发短信警报吧。

MySQL主从配置的一些总结

2011-11-28 15:57 抚琴煮酒 51CTO 我要评论(0) 字号： T | T

有很多朋友做了mysql主从也有一段时间了，但是有时候也走了不少弯路，时间也浪费了不少，主要问题是没有查阅其他的主机配置的相关资料，而仅仅是看了配置文档，下面是作者对主从配置的一些总结。

AD：干货来了,不要等！WOT2015 北京站演讲PPT开放下载！

【51CTO独家特稿】一、做了MySQL主从也有一段时间了，这两天检查磁盘空间情况，发现放数据库的分区磁盘激增了40多G，一路查看下来，发现配置好主从复制以来到现在的binlog就有40多G，原来根源出在这里，查看了一下my.cnf，看到binlog的 size是1G就做分割，但没有看到删除的配置，在MySQL里show了一下variables：

作者个人博客：andrewyu.blog.51cto.com

   mysql>show variables like '%log%';

查到了，

   | expire_logs_days | 0 |

这个默认是0，也就是logs不过期，这个是一个global的参数，所以需要执行

   set global expire_logs_days=8;

这样8天前的log就会被删除了，如果有回复的需要，请做好备份工作，但这样设置还不行，下次重启mysql了，配置又恢复默认了，所以需在my.cnf中设置，

   expire_logs_days = 8

这样重启也不怕了。

现在我在生产环境下的做法是将此时间设为0，然后备份mysql日志文件，然后再手动清理此文件。

想要恢复数据库以前的资料,执行

   mysql>show binlog events;

由于数据量很多，查看起来很麻烦，光打开个文件就要闪半天，所以应该适当删除部分可不用的日志。

并且如果使用的时间足够长的话，会把我的硬盘空间都给吃掉。

1、登录系统，/usr/bin/mysql

使用mysql查看日志：

   mysql>show binary logs; 
+—————-+———–+ 
| Log_name | File_size | 
+—————-+———–+ 
| ablelee.000001 | 150462942 | 
| ablelee.000002 | 120332942 | 
| ablelee.000003 | 141462942 | 
+—————-+———–+

2、删除bin-log(删除ablelee.000003之前的而没有包含ablelee.000003)：

   mysql> purge binary logs to ′ablelee.000003′; 
Query OK, 0 rows affected (0.16 sec)

3、查询结果(现在只有一条记录了)：

   mysql> show binlog events\G  
*************************** 1. row ***************************  
Log_name: ablelee.000003  
Pos: 4  
Event_type: Format_desc  
Server_id: 1  
End_log_pos: 106  
Info: Server ver: 5.1.26-rc-log, Binlog ver: 4  
1 row in set (0.01 sec)  
(ablelee.000001和ablelee.000002已被删除)  
mysql> show binary logs;  
+—————-+———–+  
| Log_name | File_size |  
+—————-+———–+  
| ablelee.000003 | 106 |  
+—————-+———–+  
1 row in set (0.00 sec)  
(删除的其它格式运用!)  
PURGE {MASTER | BINARY} LOGS TO ‘log_name’  
PURGE {MASTER | BINARY} LOGS BEFORE ‘date’

用于删除列于在指定的日志或日期之前的日志索引中的所有二进制日志。这些日志也会从记录在日志索引文件中的清单中被删除，这样被给定的日志成为第一个。

例如：

   PURGE MASTER LOGS TO 'mysql-bin.010'; 
PURGE MASTER LOGS BEFORE '2008-06-22 13:00:00';

二、现在手上蛮多项目的数据库用的是MySQL，由于权限等原因，暂时不方便部署Nagios监控MySQL主从复制，所以我一般在从机上配置了SHELL脚本用来监控MySQL的主从状态(设置为每十分钟运行一次)，并且每次出问题时将确切日期写进错误日志，方便事后排查原因，脚本内容如下：

   #!/bin/bash 
#check MySQL_Slave Status 
#crontab time 00:10 
MYSQLPORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $4}'` 
MYSQLIP=`ifconfig eth0|grep "inet addr" | awk -F[:" "]+ '{print $4}'` 
STATUS=$(/usr/local/webserver/mysql/bin/mysql -u yuhongchun -pyuhongchun101 -S /tmp/mysql.sock -e "show slave status\G" | grep -i "running") 
IO_env=`echo $STATUS | grep IO | awk ' {print $2}'` 
SQL_env=`echo $STATUS | grep SQL | awk '{print $2}'` 
 
 
if [ "$MYSQLPORT" == "3306" ] 
then 
echo "mysql is running" 
else 
mail -s "warn!server: $MYSQLIP mysql is down" yuhongchun027@163.com 
fi 
 
if [ "$IO_env" = "Yes" -a "$SQL_env" = "Yes" ] 
then 
echo "Slave is running!" 
else 
echo "####### $date #########">> /data/data/check_mysql_slave.log 
echo "Slave is not running!" >> /data/data/check_mysql_slave.log 
mail -s "warn! $MySQLIP_replicate_error" yuhongchun027@163.com << /data/data/check_mysql_slave.log 
fi

建议每十分钟运行一次。

   */10 * * * * root /bin/sh /root/mysql_slave.sh

记得在每台MySQL从机上分配一个yuhongchun的用户，权限大些也没关系，只限定在本地运行，如下所示：

   grant all privileges on *.* to "yuhongchun"@"127.0.0.1" identified by "yuhongchun101"; 
grant all privileges on *.* to "yuhongchun"@"localhost" identified by "yuhongchun101";

脚本设计思路：

1、此脚本应该能适应各种各样不同的内外网环境，即IP不同的环境；

2、让脚本也顺便监控下MySQL是否正常运行；

三、innodb_buffer_pool_size的设置。

这个参数定义了InnodDB存储引擎的表数据和索引数据的最大内存缓冲区大小。和MyISAM存储引擎不同，MyISAM的key_buffer_size只缓存索引键，而innodb_buffer_pool_size却是同时为数据块和索引块做缓存，这个特征和Oracle是一样的，这个值设得越高，访问表中数据需求的I/O就越少。在一个专用的数据库服务器，可以设置这个参数达机器物理内存的80%，我现在一般的做法是配置成物理内存的 1/4，比如8G内存的生产数据库，我一般会配置成2G左右。

四、测试了很长一段时间的MySQL的负载均衡，最后综合了老男孩和其它技术高手的意见，最终决定还是用LVS+Keepalived来作为MySQL的负载均衡，这是因为后端机器超过10台时，LVS的性能还是最好的；如果在3-5台左右，HAProxy也可以很轻松的搞定工作。

五、大家都很清，磁盘I/O总会成为数据库的性能瓶颈，这时候我们应该如何在生产环境下选择合适的RAID级别呢？

1、如果数据读写都很频繁，可靠性要求也很高，最好选择RAID10；

2、如果数据读很频繁，写相对较少，对可靠性有一定要求，可以选择RAID5；

3、如果数据读写都很频繁，但可靠性要求不高，可以选择RAID0。

4、对于核心业务的数据库主从同步，建议从机的备份时间往后延迟一段时间，通常的做法是延迟一天左右。

作者介绍：

余洪春（抚琴煮酒），《构建高可用Linux服务器》一书作者，一拍网系统架构师、资深项目管理工程师，ChinaUnix集群和高可用版版主。