mysql 主从复制

老样子，我们再本地来配置这个主从，没法避免的就是需要两套配置文件，闲话不说了，开始搞起。主的配置文件 /Users/olifer/middle/mysql/master/my.cnf

[client]
port = 3306

[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /Users/olifer/middle/mysql/master/data
pid-file = /Users/olifer/middle/mysql/master/data/mysql.pid
server-id = 1

log-bin = mysql-bin
binlog_format = ROW
log-bin-index = master-bin.index


lower_case_table_names=1

从的配置文件 /Users/olifer/middle/mysql/slave/my.cnf

[client]
port = 3307
socket = /Users/olifer/middle/mysql/slave/socket/mysql.sock


[mysqld]
port = 3307
socket = /Users/olifer/middle/mysql/slave/socket/mysql.sock

basedir = /usr/local/mysql
datadir = /Users/olifer/middle/mysql/slave/data
pid-file = /Users/olifer/middle/mysql/slave/data/mysql.pid
server-id = 2

log_bin=mysql-bin 
log_slave_updates=1
read_only=1
relay_log=/Users/olifer/middle/mysql/slave/relaylog

lower_case_table_names=1

主的服务器的端口占用的是3306，从的占用的而是3307.另外他们的datadir的位置也不同，防止数据串掉。配置文件配置好了，我们就可以下面的操作了

创建用户

在使用配置文件启动的时候会查找user，不知道大家记不记得，使用安装包安装mysql时有一个步骤是创建默认用户的，然后给你一个随机字符串作为密码，登录进去后，就可以修改为及定义的密码了，使用配置文件启动也是一样的道理，需要设置用户.

mysqld --initialize --user=root  --basedir=/usr/local/mysql --datadir=/Users/olifer/middle/mysql/master/data

创建一个root的用户，并且指定datadir的地址/Users/olifer/middle/mysql/master/data，会将初始化的信息放到这个文件里面，mysql后续使用配置文件启动的时候也配置了这个文件的路径，登录验证的时候会读取用户名和密码。

mysqld --initialize --user=root  --basedir=/usr/local/mysql --datadir=/Users/olifer/middle/mysql/master/data

2017-12-05T06:46:32.971852Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-05T06:46:32.980187Z 0 [Warning] Setting lower_case_table_names=2 because file system for /Users/olifer/middle/mysql/master/data/ is case insensitive
2017-12-05T06:46:32.980963Z 0 [Warning] One can only use the --user switch if running as root

2017-12-05T06:46:33.407405Z 0 [Warning] InnoDB: New log files created, LSN=45790
2017-12-05T06:46:33.468393Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2017-12-05T06:46:33.582314Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 084843d0-d988-11e7-9da8-bda1f0d03fcc.
2017-12-05T06:46:33.586449Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-12-05T06:46:33.592723Z 1 [Note] A temporary password is generated for root@localhost: i*d_Rf%o;5wt

可以看到这个root用户生成的默认的密码是i*d_Rf%o;5wt。下面我们就可以指定配置文件启动了。

mysqld --defaults-file="/Users/olifer/middle/mysql/master/my.cnf"

指定一下配置文件的路径就行，如果发现下面的日志，说明启动成功

2017-12-05T06:52:53.733465Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-12-05T06:52:53.733671Z 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2017-12-05T06:52:53.733709Z 0 [Note] mysqld (mysqld 5.7.20-log) starting as process 84538 ...
2017-12-05T06:52:53.761297Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2017-12-05T06:52:53.761318Z 0 [Note] InnoDB: Uses event mutexes
2017-12-05T06:52:53.761323Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2017-12-05T06:52:53.761327Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2017-12-05T06:52:53.761673Z 0 [Note] InnoDB: Number of pools: 1
2017-12-05T06:52:53.761836Z 0 [Note] InnoDB: Using CPU crc32 instructions
2017-12-05T06:52:53.763323Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2017-12-05T06:52:53.775036Z 0 [Note] InnoDB: Completed initialization of buffer pool
2017-12-05T06:52:53.790858Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2017-12-05T06:52:53.796454Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2017-12-05T06:52:53.796579Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2017-12-05T06:52:53.846152Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2017-12-05T06:52:53.846943Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-12-05T06:52:53.846955Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2017-12-05T06:52:53.847177Z 0 [Note] InnoDB: Waiting for purge to start
2017-12-05T06:52:53.899386Z 0 [Note] InnoDB: 5.7.20 started; log sequence number 2565396
2017-12-05T06:52:53.899594Z 0 [Note] InnoDB: Loading buffer pool(s) from /Users/olifer/middle/mysql/master/data/ib_buffer_pool
2017-12-05T06:52:53.899762Z 0 [Note] Plugin 'FEDERATED' is disabled.
2017-12-05T06:52:53.901325Z 0 [Note] InnoDB: Buffer pool(s) load completed at 171205 14:52:53
2017-12-05T06:52:53.939671Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-12-05T06:52:53.939701Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-12-05T06:52:53.940143Z 0 [Note] IPv6 is available.
2017-12-05T06:52:53.940172Z 0 [Note]   - '::' resolves to '::';
2017-12-05T06:52:53.940256Z 0 [Note] Server socket created on IP: '::'.
2017-12-05T06:52:53.952979Z 0 [Note] Event Scheduler: Loaded 0 events
2017-12-05T06:52:53.953191Z 0 [Note] mysqld: ready for connections.
Version: '5.7.20-log'  socket: '/Users/olifer/middle/mysql/master/socket/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2017-12-05T06:52:53.953208Z 0 [Note] Executing 'SELECT * FROM INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check.
2017-12-05T06:52:53.953213Z 0 [Note] Beginning of list of non-natively partitioned tables
2017-12-05T06:52:53.966341Z 0 [Note] End of list of non-natively partitioned tables

mysql 服务起来了，我们登录进去试一下

mysql -u root -p -h 127.0.0.1 -P 3306
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log

Copyright (c) 2000, 2017, 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>

使用生成的随机码进入，我们下一步可以修改一下root用户的密码，因为太难记了。

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.03 sec)

将root用户的密码也修改成root。之后我们创建一个新的用户，专门用来做主从数据同步的

mysql> grant replication slave,replication client on *.* to repl@'127.0.0.1' identified by 'repl';
Query OK, 0 rows affected, 1 warning (0.02 sec)

我们看一下master的状态

mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      709 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

主的配置我们就结束了。下面我们载配置从服务器，前面的配置就不说了，包括创建用户登录，修改密码等。我们直接配置从是怎么连接上主服务器的。

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> change master to master_user='repl',master_password='repl',master_host='127.0.0.1',master_port=3306,master_log_file='mysql-bin.000005',master_log_pos=709;
Query OK, 0 rows affected, 2 warnings (0.10 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

主要是看一下change master 命令设置的属性，master_user与master_password就是我们再主服务器上设置的同步的用户与密码，master_host与master_port是主服务器的ip与端口master_log_file与master_log_pos对应的就是show master status命令时对应的File与Position的值，标示从服务器是从哪个点开始同步。配置完后，重启slave即可。目前为止，配置的信息都结束了。我们看一下配置的效果。

在主服务器上执行创建数据库的操作

mysql> create database test2;
Query OK, 1 row affected (0.03 sec)

在从服务器上查看一下

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

数据已经同步过来了。在从上创建一个数据库

mysql> create database test3;
Query OK, 1 row affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
| test3              |
+--------------------+
6 rows in set (0.00 sec)

然后再主服务上查看

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

mysql>

mysql 主从复制

创建用户

推荐阅读更多精彩内容