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/mysqld basedir=/usr/local/mysql/ datadir=/usr/local/mysql/data/mysql [root@Sandos1 mysql]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL.. SUCCESS!
三：设置mysql密码，以及在master创建数据库
[root@Sandos mysql]# mysql
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.
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 mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> 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: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> create database repl;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye
四：master以及slave配置
修改master的my.cnf配置
[root@Sandos mysql]# grep -v "^#" /etc/my.cnf [mysqld] server-id=1 log-bin=mysql-bin log-slave-updates=1 binlog-do-db=repl #需要同步的数据库，如果不配置则表示所有 binlog-ignore-db=mysql #被忽略的数据库
进入master mysql设置slave同步的账号
[root@Sandos mysql]# /etc/init.d/mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! [root@Sandos mysql]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 1 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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-bin read-only=1 log-slave-updates=1 replicate-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 2 Server 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 its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
master查看给salve的权限
[root@Sandos mysql]# mysql -uroot -p123456 Warning: 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 3 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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 statusG;
*************************** 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: 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
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: 0
1 row in set (0.00 sec)
五、测试主从是否同步
进入master创建测试表
mysql> use repl Database changed mysql> 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-retry
master-connect-retry这个参数是用来设置在和主服务器连接丢失的时候，重试的时间间隔，默认是60秒
(3) read-only
read-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;