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