MYSQL 的 AB 复制（主从，双机热备）

435421

MYSQL 的 AB 复制（主从，双机热备）
    在实际的应用中，我们经常需要制作一个备份数据库。当我们的主数据库发生问题导致无法正常相应的时候，我们可以
把备用的从数据库街上来顶替主库，以达到不间断服务，热备的作用。在实际应用中从库和主库的数据必须同步，保持一致，
对主库的任何操作通要引发从库上的同样操作。
实现原理：
从库以一定的频率去读取主库的二进制日志文件，按照日志中记录对从库进行同样的操作，以达到同步效果。
注意版本影响：
实现双机的热备首先要了解主从数据库服务器的版本的需求。首先，MYSQL的版本都要高于3.2，还有一个基本的原则就是作
为从数据库的数据库版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本
实验环境：
  IP地址主机名
主库：192.168.200.101  master
从库：192.168.200.10slave
两台机器都要安装软件包：mysql mysql-srver
# yum -y install mysql mysql-server
主库操作：
# vim /etc/my.cnf 在mysqld中填加如下配置
server-id=1
log-bin=mysql-binlog
expire_logs_day=10
max_binlog_size=500M
# service mysqld restart
主库增加用户，用于读取主库日志
# mysql -u root -p
mysql> grant replication slave,reload,super on *.* to slave@192.168.200.10 identified by '123456';
replication slave：用于复制型从属服务器（从主服务器中读取二进制日志事件）
reload：允许使用FLUSH
super：允许使用 CHANG MASTER,KILL,PURGE,MASTER LOGS,SET GLOBAL 等语句
获得 MasterDB 的相关信息
mysql> show master status;
+------------------+----------+--------------+------------------+
| File   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |106 |
||
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
记录下File和Position的值。然后登录从服务器，进行如下操作：
mysql> FLUSH TABLES WITH READ LOCK;
# mysqldump --all-databases > db.backuo.sql
# scp db.backuo.sql root@192.168.200.10
从库操作：
从库连接主库进行测试，如果连接成功说明主库配置成功
# mysql -u slave -p123456 -h 192.168.200.101
# service mysqld restart
# mysql -u root -p < db.backup.sql
停从库，修改从库/etc/my.cnf，增加选项：
vim /etc/my.cnf
[mysqld]
server-id=2
master-host=192.168.200.101
master-user=slave
master-password=123456
5、启动从库，进行主从库数据同步
# service mysqld restart
# mysql -u root -p
mysql> stop slave;
mysql> reset slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.200.101',
-> MASTER_USER='slave',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=106;
Query OK, 0 rowsaffected (0.04 sec)
mysql> start slave;
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name', //主服务器的IP地址
-> MASTER_USER='replication_user_name', //同步数据库的用户
-> MASTER_PASSWORD='replication_password', //同步数据库的密码
-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名(前面要求记录的参数)
-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记录的参数)
报错解决方法：
ERROR 1201(HY000):Could now initialize master info structure; more error messages can be found in the MySQL error log
stop slave;
reset slave;
CHANGE MASTER TO MASTER_HOST='192.168.3.254', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-binlog.000003', MASTER_LOG_POS=246;
start slave;
数据不同步解决办法：
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
进行测试：
主库查看当前存在的库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
从库查看当前存在库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)
说明两者中的数据保持了一致性
主库创建库，表：
mysql> create database sampdb;
mysql> use sampdb
mysql> create table new(name char(20),phone char(20));
打开从库，查看：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sampdb             |
| test               |
+--------------------+
4 rows in set (0.00 sec)
mysql> use sampdb
mysql> show tables;
说明主从数据库创建成功。
主从数据库相关命令：
slave stop; slave start; 开始停止从数据库。
show slave status\G; 显示从库状态信息
show master status\G;显示主库状态信息
purge master logs to ’binlog.000004’; 此命令非常小心，删除主数据库没用的二进制日志文件。如果误删除，那么从库就没有办法自动更新了。
change master；从服务器上修改参数使用
另外，如果你当前操作的从库以前曾经与其他服务器建立过主从关系，你可能会发现即使你在my.cnf文件中即便更改了主服务器的位置，但是MSQL仍然
在试图连接就旧的主服务器的现象。发生这种问题的时候，我们可以通过清除master.info这个缓存文件或者在mysql中通过命令来进行设置。方式如下：
a、删除master.info方法
这个文件位于数据文件存放目录里。默认是在/var/lib/mysql中的。你可以直接将其删除，然后重新启动服务器。
b、mysql命令方法
如果你不方便重新启动服务器的话，那么就只能使用mysql命令来帮助你做到。
首先登录到主服务器上，查看当前服务器状态：
mysql> show master status\G;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+
1.FLUSH TABLES WITH READ LOCK
这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行。
解锁的语句也是unlock tables。
2.LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
这个命令是表级别的锁定，可以定制锁定某一个表。例如： lock  tables test read; 不影响其他表的写操作。
解锁语句也是unlock tables。
这两个语句在执行的时候都需要注意个特点，就是 隐式提交的语句。在退出mysql终端的时候都会隐式的执行unlock tables。也就是如果要让表锁定生效就必须一直保持对话。
P.S.  MYSQL的read lock和wirte lock
read-lock:  允许其他并发的读请求，但阻塞写请求，即可以同时读，但不允许任何写。也叫共享锁
write-lock: 不允许其他并发的读和写请求，是排他的(exclusive)。也叫独占锁
3. flush table tablename