mysql数据库主备同步部署

依然饭跑跑

　　数据库主从同步部署
　　约定：
　　主数据库master  ip：10.0.0.4:3306
　　备数据库slave   ip：10.0.0.3:3306
　　部署流程：
　　主数据库操作如下
　　1、开启主数据的binlog参数，和设置server-id
　　2、创建用于同步数据的账号rep
　　3、锁表并查看当前日志名称和位置
　　4、备份当前主数据库的全部数据（全备）
　　5、给主数据解锁。让主库继续提供服务
　　6、继续往主数据写数据
　　备数据库操作
　　1、把主数据备份的全备数据备份到备数据库
　　2、在备数据库上设置主从同步的相关信息，如主数据库的IP、端口、同步账号、密码、binlog文件名、binlog位置点
　　3、开始主从同步start slave；
　　4、查看是否备份成功，show slave status\G;
　　############################################################################
　　主数据库上面操作
　　开启主数据库的binlog参数，和设置server-id
　　egrep "server-id|log-bin" /etc/my.cnf
　　########################################
　　[root@M_MYSQL ~]# egrep "server-id|log-bin" /etc/my.cnf
　　server-id       = 1
　　log-bin=mysql-bin
　　[root@M_MYSQL ~]#
　　重启3306实例数据库
　　/etc/init.d/mysqld restart
　　netstat -lntup|grep 3306
　　########################################
　　[root@M_MYSQL ~]# /etc/init.d/mysqld restart
　　Shutting down MySQL. SUCCESS!
　　Starting MySQL.. SUCCESS!
　　[root@M_MYSQL ~]# netstat -lntup|grep 3306
　　tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      2412/mysqld
　　[root@M_MYSQL ~]#
　　########################################
　　查看是否记录bin_log日志
　　[root@M_MYSQL ~]# ll /application/mysql/data/
　　total 28700
　　-rw-rw----. 1 mysql mysql 18874368 Sep 30 21:58 ibdata1
　　-rw-rw----. 1 mysql mysql  5242880 Sep 30 21:58 ib_logfile0
　　-rw-rw----. 1 mysql mysql  5242880 Sep 30 21:33 ib_logfile1
　　-rw-r-----. 1 mysql root      3458 Sep 30 21:58 M_MYSQL.err
　　-rw-rw----. 1 mysql mysql        5 Sep 30 21:58 M_MYSQL.pid
　　drwx------. 2 mysql root      4096 Sep 30 21:33 mysql
　　-rw-rw----. 1 mysql mysql      107 Sep 30 21:58 mysql-bin.000001          ##有这个，证明在记录主从同步
　　-rw-rw----. 1 mysql mysql       19 Sep 30 21:58 mysql-bin.index
　　drwx------. 2 mysql mysql     4096 Sep 30 21:33 performance_schema
　　drwxr-xr-x. 2 mysql mysql     4096 Sep 30 21:33 test
　　[root@M_MYSQL ~]#
　　########################################
　　##在主数据库中创建测试用的数据库和表。
　　create database lvnian;
　　use lvnian;
　　create table test(
　　id int(4) not null auto_increment,
　　name char(20) not null,
　　primary key(id)
　　);
　　flush privileges;
　　mysql -uroot -plvnian -e "use lvnian; select * from test;"
　　for n in `seq 100`;do mysql -uroot -plvnian -e "use lvnian;insert test values($n,'lvnian$n'); " ;done;
　　mysql -uroot -plvnian -e "use lvnian; select * from test;"
　　############################################
　　##############
　　查看是否开启binlog
　　[root@M_MYSQL ~]#  mysql -uroot -plvnian -e "show variables like 'log_bin'"
　　+---------------+-------+
　　| Variable_name | Value |
　　+---------------+-------+
　　| log_bin       | ON    |
　　+---------------+-------+
　　[root@M_MYSQL ~]#
　　############################################
　　创建用于同步数据的账号rep

　　mysql> grant replication slave on *.* to 'rep'@'10.0.0.%'>　　Query OK, 0 rows affected (0.00 sec)
　　mysql> flush privileges;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql -uroot -plvnian -e "select user,host from mysql.user;"
　　########################################
　　[root@M_MYSQL ~]#  mysql -uroot -plvnian -e "select user,host from mysql.user;"
　　+------+-----------+
　　| user | host      |
　　+------+-----------+
　　| rep  | 10.0.0.%  |
　　| root | 127.0.0.1 |
　　############################################
　　锁表
　　mysql> flush table with read lock;
　　Query OK, 0 rows affected (0.00 sec)
　　################################################################################
　　查看当前日志名称和位置
　　mysql -uroot -plvnian -e " show master status;"
　　mysql -uroot -plvnian -e "show master logs;"
　　########################################
　　[root@M_MYSQL ~]# mysql -uroot -plvnian -e " show master status;"
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000003 |      302 |              |                  |
　　+------------------+----------+--------------+------------------+
　　[root@M_MYSQL ~]# mysql -uroot -plvnian -e "show master logs;"
　　+------------------+-----------+
　　| Log_name         | File_size |
　　+------------------+-----------+
　　| mysql-bin.000001 |     20802 |
　　| mysql-bin.000002 |       150 |
　　| mysql-bin.000003 |       302 |
　　+------------------+-----------+
　　[root@M_MYSQL ~]#
　　######################################
　　备份当前主数据库数据
　　另开一个窗口
　　mysqldump -uroot  -plvnian -A -B -F --master-data=2 | gzip > /tmp/mysql_bak.$(date +%F).sql.gz
　　##################
　　[root@M_MYSQL ~]#  mysqldump -uroot  -plvnian -A -B -F --master-data=2 --events | gzip > /tmp/mysql_bak.$(date +%F).sql.gz
　　[root@M_MYSQL ~]# ll /tmp/
　　total 148
　　-rw-r--r--. 1 root  root  145225 Sep 30 22:19 mysql_bak.2015-09-30.sql.gz
　　[root@M_MYSQL ~]#
　　给主数据库解锁
　　mysql> unlock tables;
　　Query OK, 0 rows affected (0.00 sec)
　　###############################################
　　继续往主数据库写数据
　　for n in `seq 100 150`;do mysql -uroot -plvnian -e "use lvnian;insert test values($n,'lvnian$n'); " ;done;
　　mysql -uroot -plvnian -e "use lvnian; select * from test;"
　　############################################################################
　　############################################################################
　　############################################################################
　　############################################################################
　　启动备数据库
　　/etc/init.d/mysqld restart
　　[root@S_MYSQL ~]# /etc/init.d/mysqld restart
　　Shutting down MySQL. SUCCESS!
　　Starting MySQL.. SUCCESS!
　　[root@S_MYSQL ~]# netstat -lntup|grep 3306
　　tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      2475/mysqld
　　[root@S_MYSQL ~]#
　　#####################################################
　　把备份数据备份到备数据库
　　scp root@10.0.0.4:/tmp/mysql_bak.2015-09-30.sql.gz .
　　#####################################
　　[root@S_MYSQL ~]# scp root@10.0.0.4:/tmp/mysql_bak.2015-09-30.sql.gz .
　　root@10.0.0.4's password:
　　mysql_bak.2015-09-30.sql.gz                                   100%  142KB 141.8KB/s   00:00
　　[root@S_MYSQL ~]# ls
　　anaconda-ks.cfg  aq  deng.txxt  install.log  install.log.syslog  mysql_bak.2015-09-30.sql.gz
　　[root@S_MYSQL ~]#
　　##########
　　[root@S_MYSQL ~]# gzip -d mysql_bak.2015-09-30.sql.gz
　　[root@S_MYSQL ~]# mysql -uroot -plvnian < mysql_bak.2015-09-30.sql
　　[root@S_MYSQL ~]# mysql -uroot -plvnian -e "show databases;"
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| lvnian             |
　　| mysql              |
　　| performance_schema |
　　| test               |
　　+--------------------+
　　[root@S_MYSQL ~]# mysql -uroot -plvnian -e "use lvnian;select * from test;"
　　+-----+-----------+

　　|>　　+-----+-----------+
　　|   1 | lvnian1   |
　　|   2 | lvnian2   |
　　|   3 | lvnian3   |
　　|   4 | lvnian4   |
　　|   5 | lvnian5   |
　　|   6 | lvnian6   |
　　|   7 | lvnian7   |
　　|   8 | lvnian8   |
　　|   9 | lvnian9   |
　　|  10 | lvnian10  |
　　|  11 | lvnian11  |
　　|  12 | lvnian12  |
　　|  13 | lvnian13  |
　　....
　　....
　　....
　　全部恢复到备数据库成功
　　###############################################
　　开始设置主从同步
　　###################################
　　更加主数据库刚才的文件名称和位置点，等到下面的内容
　　CHANGE MASTER TO
　　MASTER_HOST='10.0.0.4',
　　MASTER_PORT=3306,
　　MASTER_USER='rep',
　　MASTER_PASSWORD='lvnian123456',
　　MASTER_LOG_FILE='mysql-bin.000003',
　　MASTER_LOG_POS=302;
　　######################
　　start slave;
　　show  slave status\G;
　　###################################
　　mysql> CHANGE MASTER TO
　　-> MASTER_HOST='10.0.0.4',
　　-> MASTER_PORT=3306,
　　-> MASTER_USER='rep',
　　-> MASTER_PASSWORD='lvnian123456',
　　-> MASTER_LOG_FILE='mysql-bin.000003',
　　-> MASTER_LOG_POS=302;
　　Query OK, 0 rows affected (0.08 sec)
　　mysql>
　　mysql> start slave;
　　Query OK, 0 rows affected (0.03 sec)
　　mysql> show  slave status\G;
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 10.0.0.4
　　Master_User: rep
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000004
　　Read_Master_Log_Pos: 9759
　　Relay_Log_File: S_MYSQL-relay-bin.000005

　　>
　　>　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB:
　　Replicate_Ignore_DB:
　　Replicate_Do_Table:
　　Replicate_Ignore_Table:
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 9759

　　>　　Until_Condition: None
　　Until_Log_File:
　　Until_Log_Pos: 0
　　Master_SSL_Allowed: No
　　Master_SSL_CA_File:
　　Master_SSL_CA_Path:
　　Master_SSL_Cert:
　　Master_SSL_Cipher:
　　Master_SSL_Key:
　　Seconds_Behind_Master: 0
　　Master_SSL_Verify_Server_Cert: No
　　Last_IO_Errno: 0
　　Last_IO_Error:
　　Last_SQL_Errno: 0
　　Last_SQL_Error:
　　Replicate_Ignore_Server_Ids:
　　Master_Server_Id: 1
　　1 row in set (0.00 sec)
　　ERROR:
　　No query specified
　　mysql>
　　成功的表现：
　　[root@S_MYSQL ~]# mysql -uroot -plvnian -e "show  slave status\G;"|egrep "Slave_IO_Runnin|Slave_SQL_Running"
　　Slave_IO_Running: Yes
　　Slave_SQL_Running: Yes
　　[root@S_MYSQL ~]#
　　出现上面两个yes证明数据库主从同步成功了
　　################################################################################
　　小结
　　注意防火墙问题
　　注意主从数据库的server-id问题，主从数据库的server-id必须是不同的。
　　注意主数据库的配置文件my.cnf 必须开启记录binlog日志
　　################################################################################
　　同步故障解决
　　下面主从不同步的原因就是上面从库的server-id没有修改，导致主从数据库的server-id是一样的，就会出现下面错误:
　　注意错误提示：

　　Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server>　　mysql> show  slave status\G;
　　*************************** 1. row ***************************
　　Slave_IO_State:
　　Master_Host: 10.0.0.4
　　Master_User: rep
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000003
　　Read_Master_Log_Pos: 302
　　Relay_Log_File: S_MYSQL-relay-bin.000001

　　>
　　>　　Slave_IO_Running: No
　　Slave_SQL_Running: Yes
　　Replicate_Do_DB:
　　Replicate_Ignore_DB:
　　Replicate_Do_Table:
　　Replicate_Ignore_Table:
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 302

　　>　　Until_Condition: None
　　Until_Log_File:
　　Until_Log_Pos: 0
　　Master_SSL_Allowed: No
　　Master_SSL_CA_File:
　　Master_SSL_CA_Path:
　　Master_SSL_Cert:
　　Master_SSL_Cipher:
　　Master_SSL_Key:
　　Seconds_Behind_Master: NULL
　　Master_SSL_Verify_Server_Cert: No
　　Last_IO_Errno: 1593

　　Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server>　　Last_SQL_Errno: 0
　　Last_SQL_Error:
　　Replicate_Ignore_Server_Ids:
　　Master_Server_Id: 1
　　1 row in set (0.00 sec)
　　上面同步不成功的原因是。没有修改备数据库的server-id .导致备数据库和主数据库的server-id相同。把主备数据库的server-id改为不一致就可以了