-- My SQL 集群搭建成功 --

Question

-- 新进一公司，用的是My SQL数据库!-- 现在：公司有一生产库，mysqldump 执行，备份文件下来(不压缩)有近20G，需要备份时间2小时左右，
-- 同时若在原库的基础上搭建集群(先考虑主----主集群)的话，需要拷贝文件15分钟，恢复到另一主库需要2小时，
-- 这样下来生产库总共需要停机4个多小时(不出意外)。-- 公司是一个团购公司，4个小时的停机时间(每小时损失约5万)，这样算来来的话，公司损失近20万，自然是难以忍受的!-- 所以：我采用的是增量备份与恢复的方法，只要半个小时完事儿，为公司挽回损失20万!-- 操作步骤大致如下：
-- 先执行增量备份的完全备份：(备份前，我先看一下当前log日志的最大文件号)
mysql> show master status\G
*************************** 1. row ***************************
            File: mysql-bin.000630
        Position: 78686586
    Binlog_Do_DB: groupon
Binlog_Ignore_DB: 
1 row in set (0.00 sec)-- 所以：当我执行下面的备份操作时，因为加了flush-logs参数，
-- 在执行备份这个时间点上，My SQL会自动切换日志文件(例如：生成 mysql-bin.000631)mysqldump -uroot -pyourpassword db_name --single-transaction --flush-logs --master-data=2 --delete-master-logs > bk_db_name_20110719.sql &-- 备份后，打包，压缩，拷贝到远程服务器(集群的另一主节点)，进行恢复：
/var/mysql/bin/mysql -uroot -pyourpassword db_name < /var/www/mysqlBackup/bk_239_db/bk_db_name_20110719.sql &-- 恢复操作成功后，假设当前的源库的日志文件为： mysql-bin.000633
-- 那么，我们还需要：
-- 首先：切换源库的日志文件：
mysql> flush logs-- 此时：源库当前的日志文件应该为：mysql-bin.000634
-- 我们先将源库的日志文件：mysql-bin.000631 mysql-bin.000632 mysql-bin.000633 拷贝到将要搭建集群的另一主节点，并进行日志应用(拷贝操作就略啦)
/var/mysql/bin/mysqlbinlog -uroot -pyourpassword -ddb_name mysql-bin.000631 mysql-bin.000632 mysql-bin.000633 | /var/mysql/bin/mysql -uroot -pyourpassword db_name &-- 最后，
-- 先在目标库授权 (另一主节点，假设源库所在网段为：192.168.10，源库的IP地址为：192.168.10.111)
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.10.%' IDENTIFIED BY 'yourpassword';change master to master_host='192.168.10.111',master_port=3306,master_user='repl',master_password='yourpassword',master_log_file='mysql-bin.000634',master_log_pos=1;-- 上面语句，表示 从源库的mysql-bin.000634日志文件的起始位置(master_log_pos=1)开始同步应用日志。
-- 最后：此时可以关掉源库的所有Web应用，设置源库与目标库的日志同步-- 最后：两台主库
start slave;-- 查看状态：
show slave status\G...
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...--  一切正常，搭建成功!-- 你甚至可以用主键表查询验证：-- 在主库1执行以下查询：
mysql>  select max(id) from t_Groupon_order;
+---------+
| max(id) |
+---------+
| 6407093 |
+---------+
1 row in set (0.00 sec)
mysql> select count(id) from t_Groupon_order where id<=6407093;
+-----------+
| count(id) |
+-----------+
|   6253317 |
+-----------+
1 row in set (47.08 sec)
-- 在主库2执行以下查询：
mysql> select count(id) from t_Groupon_order where id<=6407093;
+-----------+
| count(id) |
+-----------+
|   6253317 |
+-----------+
1 row in set (1 min 10.26 sec)
-- 两个主库上执行 select count(id) from t_Groupon_order where id<=6407093; 结果完全一致(一张业务单也不少)，
-- 足够说明同步成功!

Answer 1

-- 技术参考文档：mysql负载均衡完美解决方案V1.0
1.环境： 
mysql 5
ubuntu10.04 x86_64
 
mdb1        eth0    192.168.5.11 
mdb2        eth0    192.168.5.12
sdb1        eth0    192.168.5.21 
sdb2        eth0    192.168.5.22 
sdb3        eth0    192.168.5.23 
sdb4        eth0    192.168.5.24
haproxy 
        eth0    192.168.5.10    (mdb  vip write) 
        eth1    192.168.5.20    (sdb  vip read)
说明：mdb vip用于DB的写，sdb vip用于DB读，实现读写分离和负载均衡，带故障检测自动切换
 
2.架构图 
        web1    web2    web3 
         |              |          | 
        —————————- 
                    | 
            haproxy(lb db write/read) 
                    | 
        ———————————- 
        |                                | 
        mdb1                     mdb2 
          |                              | 
        ————–             —————- 
        |             |              |            | 
        sdb1    sdb2        sdb3      sdb4
说明： 
1）mdb1和mdb1配置成主-主模式，相互同步,通过haproxy提供一个lb的写ip 
2）sdb1和sdb2配置为mdb1的从，sdb3和sdb4配置为mdb2的从 
3）sdb1,sdb2,sdb3,sdb4这4台从库，通过haproxy提供一个lb的读ip 
4) 当mdb2停止复制，mdb1为主库，haproxy停止发送请求到mdb2和sdb3,sdb4 
5) 当mdb1停止复制，mdb2为主库，haproxy停止发送请求到mdb1和sdb1,sdb2 
6) 当mdb1和mdb2同时停止复制，这时2台主库变成readonly模式，数据库不能写入 
7）当mdb2 offline时，mdb1进入backup mode,停止发送请求到mdb2,sdb3,sdb4 
8）当mdb1 offline时，mdb2进入backup mode,停止发送请求到mdb1,sdb1,sdb2 
9) 当mdb1 mdb2同时offline,整个DB停止工作
 
 
3.安装mysql-server 
登录mdb1,mdb2,sdb1,sdb2,sdb3,sdb4，输入以下命令进行安装： 
apt-get install mysql-server -y
安装时会提示输入mysql root用户密码，输入gaojinbo.com
修改mysql配置，监听所有接口 
vi /etc/mysql/my.cnf 
修改为： 
bind-address            = 0.0.0.0
重启mysql 
/etc/init.d/mysql restart
 
 
4.配置mdb1,mdb2主-主同步 
1)mdb1: 
vi /etc/mysql/my.cnf 
server-id               = 1 
log_bin                 = mysql-bin
log-slave-updates                #很重要，从前一台机器上同步过来的数据才能同步到下一台机器 
expire_logs_days        = 10 
max_binlog_size         = 100M 
auto_increment_offset    = 1 
auto_increment_increment = 2
 
2)mdb2: 
vi /etc/mysql/my.cnf 
server-id               = 2 
log_bin                 = mysql-bin
log-slave-updates                #很重要，从前一台机器上同步过来的数据才能同步到下一台机器 
expire_logs_days        = 10 
max_binlog_size         = 100M 
auto_increment_offset    = 2 
auto_increment_increment = 2
 
3)mdb1和mdb2: 
重启mysql 
/etc/init.d/mysql restart
添加复制用户 
mysql -uroot -pgaojinbo.com 
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.5.%’ IDENTIFIED BY ‘gaojinbo’;
记录日志文件和pos 
mysql -uroot -pgaojinbo.com 
show master status\G
 
 
4)mdb1: 
change master to master_host=’192.168.5.12′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000003′,master_log_pos=106; 
start slave; 
show slave status\G 
说明：mysql-bin.000003和106是主库配置第3)步记录的信息
出现以下内容，说明同步ok 
           Slave_IO_Running: Yes 
          Slave_SQL_Running: Yes
 
5)mdb2: 
change master to master_host=’192.168.5.11′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000001′,master_log_pos=249; 
start slave; 
show slave status\G 
说明：mysql-bin.000001和249是主库配置第3)步记录的信息
出现以下内容，说明同步ok 
           Slave_IO_Running: Yes 
          Slave_SQL_Running: Yes
 
 
6）测试主-主同步 
mdb1: 
mysql -uroot -pgaojinbo.com 
show databases; 
create database gaojinbo;
mdb2: 
mysql -uroot -pgaojinbo.com 
show databases; 
即可看到在mdb1上建立的数据库gaojinbo
 
至此mdb1,mdb2主-主配置完成！
 
 
5.4台从库配置 
sdb1-4配置(注：server-id不能相同)： 
vi /etc/mysql/my.cnf 
server-id               = 3 
log_bin                 = mysql-bin
重启mysql 
/etc/init.d/mysql restart
 
sdb1和sdb2配置成mdb1的从库: 
mysql -uroot -pgaojinbo.com 
change master to master_host=’192.168.5.11′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000001′,master_log_pos=345; 
start slave; 
show slave status\G 
说明：mysql-bin.000001和345是主库配置第3)步记录的信息
出现以下内容，说明同步ok 
          Slave_IO_Running: Yes 
          Slave_SQL_Running: Yes
 
sdb3和sdb4配置成mdb2的从库: 
change master to master_host=’192.168.5.12′,master_port=3306,master_user=’repl’,master_password=’gaojinbo’,master_log_file=’mysql-bin.000003′,master_log_pos=106; 
start slave; 
show slave status\G 
说明：mysql-bin.000003和106是主库配置第3)步记录的信息
出现以下内容，说明同步ok 
          Slave_IO_Running: Yes 
          Slave_SQL_Running: Yes
 
测试： 
mdb1: 
mysql -uroot -pgaojinbo.com 
show databases; 
create database eossc;
在其他DB上，这时会看到刚建立的数据库eossc
 
至此4台从数据库配置完成！

Answer 2

-- 现在我能够做到的是
-- 任何生产库，要搭建集群，我只要不超过半个小时的down机时间
-- 当然：这指的是 主----主 集群-- 主----从 集群，我不需要down 机

Answer 3

汗 这不是cluster吧  在线增加DB是DBA的职责

Answer 4

在做备份文件的时候，带上--master-data，就会在备份文件里产生change master to语句，记录下来日志文件名称和POS位置，MORE看一下就可以了，不需要FLUSH LOG，记录POS这样麻烦。另外楼主把HAPROXY的配置也贴出来啊

Answer 5

我来质疑下楼主，不知道可以么？
-- 所以：我采用的是增量备份与恢复的方法，只要半个小时完事儿，为公司挽回损失20万!
-----------这话我觉得是胡说！咱们别说20万了，就说你怎么省出的4小时吧。
备份回复分冷的和热的，不管对myisam库或innodb库，mysqldump是冷备份！也就是说要锁表的，表是只读的，对一个在线服务类的库，把库中的某一个（单！）表锁住3---10分钟不让写，我看和down服务器没啥区别。热备+热恢复 当然就快了，就是copy文件，然后重启库，重启库前可做种种调节。快的话半个小时能完。但你这是热备么？

Answer 6

-- 我是经受得起质疑的!-- 首先我的 db_name 数据库中所有的表都是InnoDB引擎!-- 所以：请参考：
http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#backup5.9.2.1. 备份策略我们都知道必须按计划定期进行备份。可以用几个工具完全备份(在某个时间点的数据快照)MySQL。例如，InnoDB Hot Backup为InnoDB数据文件提供在线非数据块物理备份，mysqldump提供在线逻辑备份。这里使用mysqldump。假定我们在星期日下午1点进行了备份，此时负荷较低。下面的命令可以完全备份所有数据库中的所有InnoDB表：shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql这是在线非块备份，不会干扰对表的读写。我们以前假定我们的表为InnoDB表，因此--single-transaction使用一致性地读，并且保证mysqldump所看见的数据不会更改。(其它客户端对InnoDB表进行的更改不会被mysqldump进程看见）。如果我们还有其它类型的表，我们必须假定在备份过程中它们不会更改。例如，对于mysql数据库中的MyISAM表，我们必须假定在备份过程中没有对MySQL账户进行管理更改。

Answer 7

lz如果不是DBA的话 写这个文章很赞  如果是DBA  那还不太符合

Answer 8

罗兄，顶下，哪天发来我配置配置，怎么说我入道的时候还通读过MYSQL官方文档呢，哈哈，虽然现在已经还给MYSQL了

Answer 9

我也帮楼主回下
mysqldump里有这么一个参数This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.如果用--master-data那么就锁定所有表，除非带--single-transaction，带上这个参数，备份时只会有一个很短暂的全局锁定，用于得到MASTER的POS位置和日志文件名称，记录到备份文件里。然后用MVCC技术，得到刚才锁定时的事务前的所有数据，写入到备份文件。事务开始之后的数据，备份进程是看不到的，所以也不会出现数据乱的情况。

Answer 10

如果没有--single-transaction参数，等待全部表锁定的时间可能会很长，这时候，已经被锁定的表是不能写入的。对于MYISAM引擎的，这办法行不通。只能全局锁库，然后记录POS，然后备份，最后解库锁。
不知道还有没有其他办法对应MYISAM的。

Answer 11

嗯 用了 --single-transaction 就不锁表了，这个好！ 

Answer 12

首先，这是复制而不是集群：）
其次，楼主使用mysqldump的--single-transaction选项意味着备份期间两个小时内不允许写入，我觉得这是很不好的。
假设你的公司没有购买ibbackup在线备份工具，而且也没有使用LVM快照备份InnoDB数据库。我建议使用下面这个办法备份和恢复你需要的InnoDB数据库：
1.在准备开始备份前，在线改变系统变量innodb_max_dirty_pages_pct到一个较小的值，innodb_max_dirty_pages_pct的默认值是90%，可以把它降到30%或者更小。期间持续观察Innodb_buffer_pool_pages_dirty状态变量的值，等它降到足够小的值时关闭MySQL Server，注意关闭时不要使用innodb_fast_shutdown
选项。以上的工作都是为了缩短关闭服务器的时间。
2.修改my.cnf（ini）配置文件，使master支持复制，主要是server_id和log-bin选项。
3.拷贝表结构.frm文件和InnoDB的表空间文件后，关掉所有外界和数据库服务器的连接（确保没有人访问数据库服务器），启动mysqld。执行SHOW MASTER STATUS命令记下输出后，就可以让客户端正常访问master了。
4.假设你之前已经在新服务器上恢复了mysql数据库并且配置完毕，现在把刚刚拷贝的表结构文件和InnoDB表空间文件粘贴到这台服务器的正确位置上，启动该服务器，如果启动没问题，就可以使用CHANGE MASTER命令和START SLAVE命令启动SLAVE了。
我个人觉得这个方法避免了长达两个小时的阻塞写，只需要短暂的时间用来关闭mysqld、复制和启动mysqld。不过缺点是重启导致InnoDB可能需要一段时间热身，才能重新高效的利用InnoDB缓冲池和查询缓存（如果使用了的话）。

Answer 13

如果要是想把这个主-从复制变成主-主复制的话，只需要在启动master和slave之前分别在各自的配置文件中加入：log_slave_updates=1。然后在slave START SLAVE前，在slave上执行SHOW MASTER STATUS命令，然后在master上执行CHANGE MASTER TO，把slave作为自己的复制对象。再在slave上执行CHANGE MASTER TO，把当前的master作为复制的对象。

Answer 14

问一下楼主，你这每次用mysqldump把日志down下来，到另外一台机子上恢复，每天操作一回花半小时。为什么不建立一个实时的master--master双机，或者一对多的备份机制呢？这样基本可以不管，也不用每天操作了呀？
疑惑中。

Answer 15

楼主，你有没有一些实际参考数据比如你一台mysql支持每秒多少并发的写操作，用集群后，多少台服务器，又能支持每秒多少写操作？