mysql主从搭建
注意：不同mysql版本之间的binlog日志可能不同，所以应尽量保证mysql的版本一致，如不同，只能是slave的版本高于master版本，因为mysql的版本是向下兼容。
一：环境
mysql版本号：mysql-5.6.16
操作系统：CentOS release 6.8 (Final)
主机：192.168.32.230
主机：192.168.32.231
二：安装mysql，主从步骤一样
[root@Sandos1 local]# tar -xf mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz 100% 291MB 290.7MB/s 00:01 [root@Sandos1 local]# mv mysql-5.6.16-linux-glibc2.5-x86_64 mysql[root@Sandos1 local]# groupadd mysql[root@Sandos1 local]# useradd -g mysql mysql[root@Sandos1 local]# chown mysql:mysql mysql -R[root@Sandos1 local]# cd mysql[root@Sandos1 mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data/mysql[root@Sandos1 mysql]# cp -a support-files/mysql.server /etc/init.d/mysqld[root@Sandos1 mysql]# chmod 755 /etc/init.d/mysqld [root@Sandos1 mysql]# cp -a support-files/my-default.cnf /etc/my.cnf[root@Sandos1 mysql]# vim /etc/init.d/mysqldbasedir=/usr/local/mysql/datadir=/usr/local/mysql/data/mysql[root@Sandos1 mysql]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!
三：设置mysql密码，以及在master创建数据库
[root@Sandos mysql]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.16 MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || test |+--------------------+4 rows in set (0.00 sec)mysql> use mysqlReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> update user set password=password('123456') where user='root' and host='localhost';Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> create database repl;Query OK, 1 row affected (0.00 sec)mysql> quitBye 四：master以及slave配置
修改master的my.cnf配置
[root@Sandos mysql]# grep -v "^#" /etc/my.cnf [mysqld]server-id=1log-bin=mysql-binlog-slave-updates=1binlog-do-db=repl #需要同步的数据库，如果不配置则表示所有binlog-ignore-db=mysql #被忽略的数据库
进入master mysql设置slave同步的账号
[root@Sandos mysql]# /etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@Sandos mysql]# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.6.16-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> grant replication slave on *.* to 'repl'@'192.168.32.231' identified by '123456'; ##授权给从服务器Query OK, 0 rows affected (0.00 sec)mysql> show master status; ##查看主库的状态，file和position这两个值等会slave服务器要用 +------------------+----------+--------------+------------------+-------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 | 331 | repl | mysql | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)
配置从服务器
[root@Sandos1 ~]# cat /etc/my.cnf [mysqld]server-id=2 log-bin=mysql-binread-only=1log-slave-updates=1replicate-do-db=repl ##需要同步的数据库[root@Sandos1 ~]# /usr/local/mysql/bin/mysql -h192.168.32.230 -urepl -p ##测试到master的连通性Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.16-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
master查看给salve的权限
[root@Sandos mysql]# mysql -uroot -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.16-log MySQL Community Server (GPL)Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show grants for repl@192.168.32.231;+------------------------------------------------------------------------------------------------------------------------------+| Grants for repl@192.168.32.231 |+------------------------------------------------------------------------------------------------------------------------------+| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.32.231' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |+------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
设置slave同步
mysql> change master to master_host='192.168.32.230',master_port=3306,master_user='repl',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=120;Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave;Query OK, 0 rows affected (0.00 sec)mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.32.230 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 120 Relay_Log_File: Sandos1-relay-bin.000002 Relay_Log_Pos: 283 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes ##此处两个yes说明主从搭建成功 Replicate_Do_DB: repl 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: 120 Relay_Log_Space: 458 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: 0Master_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 Master_UUID: d0b386fd-1580-11e9-af13-000c2962f10d Master_Info_File: /usr/local/mysql/data/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 01 row in set (0.00 sec)
五、测试主从是否同步
进入master创建测试表
mysql> use replDatabase changedmysql> create table test(id int,name char(10));Query OK, 0 rows affected (0.01 sec)mysql> insert into test values(1,'cc');Query OK, 1 row affected (0.00 sec)mysql> insert into test values(2,'shama');Query OK, 1 row affected (0.01 sec)mysql> select * from test;+------+-------+| id | name |+------+-------+| 1 | cc || 2 | shama |+------+-------+2 rows in set (0.00 sec)
查看slave中的数据库
mysql> select * from test;+------+-------+| id | name |+------+-------+| 1 | cc || 2 | shama |+------+-------+2 rows in set (0.00 sec)
此时表示mysql主从已经同步
六、扩展--mysql几个重要的参数
(1) log-slave-updates
log-slave-updates这个参数用来配置从服务器的更新是否写入二进制日志，这个选项默认是不打开的，但是，如果这个从服务器B是服务器A的从服务器，同时还作为服务器C的主服务器，那么就需要开发这个选项，这样它的从服务器C才能获得它的二进制日志进行同步操作(2) master-connect-retrymaster-connect-retry这个参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒(3) read-onlyread-only是用来限制普通用户对从数据库的更新操作，以确保从数据库的安全性，不过如果是超级用户依然可以对从数据库进行更新操作(4) slave-skip-errors在复制过程中，由于各种的原因，从服务器可能会遇到执行BINLOG中的SQL出错的情况，在默认情况下，服务器会停止复制进程，不再进行同步，等到用户自行来处理。Slave-skip-errors的作用就是用来定义复制过程中从服务器可以自动跳过的错误号，当复制过程中遇到定义的错误号，就可以自动跳过，直接执行后面的SQL语句。--slave-skip-errors=[err1,err2,…….|ALL]但必须注意的是，启动这个参数，如果处理不当，很可能造成主从数据库的数据不同步，在应用中需要根据实际情况，如果对数据完整性要求不是很严格，那么这个选项确实可以减轻维护的成本master重新刷新binlog日志 mysql> flush logs;
要清楚slave执行 mysql> reset slave all;