老樣子，我們?cè)俦镜貋砼渲眠@個(gè)主從，沒法避免的就是需要兩套配置文件凿宾，閑話不說了理肺，開始搞起。主的配置文件 /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

主的服務(wù)器的端口占用的是3306扁瓢，從的占用的而是3307.另外他們的datadir的位置也不同，防止數(shù)據(jù)串掉。配置文件配置好了鞠值，我們就可以下面的操作了

創(chuàng)建用戶

在使用配置文件啟動(dòng)的時(shí)候會(huì)查找user，不知道大家記不記得渗钉，使用安裝包安裝mysql時(shí)有一個(gè)步驟是創(chuàng)建默認(rèn)用戶的彤恶，然后給你一個(gè)隨機(jī)字符串作為密碼钞钙，登錄進(jìn)去后，就可以修改為及定義的密碼了声离，使用配置文件啟動(dòng)也是一樣的道理芒炼，需要設(shè)置用戶.

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

創(chuàng)建一個(gè)root的用戶，并且指定datadir的地址/Users/olifer/middle/mysql/master/data术徊，會(huì)將初始化的信息放到這個(gè)文件里面本刽，mysql后續(xù)使用配置文件啟動(dòng)的時(shí)候也配置了這個(gè)文件的路徑，登錄驗(yàn)證的時(shí)候會(huì)讀取用戶名和密碼赠涮。

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

可以看到這個(gè)root用戶生成的默認(rèn)的密碼是i*d_Rf%o;5wt子寓。下面我們就可以指定配置文件啟動(dòng)了。

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

指定一下配置文件的路徑就行笋除，如果發(fā)現(xiàn)下面的日志斜友，說明啟動(dòng)成功

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 服務(wù)起來了，我們登錄進(jìn)去試一下

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>

使用生成的隨機(jī)碼進(jìn)入垃它，我們下一步可以修改一下root用戶的密碼鲜屏，因?yàn)樘y記了。

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

將root用戶的密碼也修改成root国拇。之后我們創(chuàng)建一個(gè)新的用戶洛史，專門用來做主從數(shù)據(jù)同步的

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的狀態(tài)

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)

主的配置我們就結(jié)束了。下面我們載配置從服務(wù)器贝奇，前面的配置就不說了虹菲，包括創(chuàng)建用戶登錄，修改密碼等掉瞳。我們直接配置從是怎么連接上主服務(wù)器的毕源。

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 命令設(shè)置的屬性 ，master_user與master_password就是我們?cè)僦鞣?wù)器上設(shè)置的同步的用戶與密碼陕习，master_host與master_port是主服務(wù)器的ip與端口master_log_file與master_log_pos對(duì)應(yīng)的就是show master status命令時(shí)對(duì)應(yīng)的File與Position的值霎褐，標(biāo)示從服務(wù)器是從哪個(gè)點(diǎn)開始同步。配置完后该镣，重啟slave即可冻璃。目前為止，配置的信息都結(jié)束了损合。我們看一下配置的效果省艳。

在主服務(wù)器上執(zhí)行創(chuàng)建數(shù)據(jù)庫的操作

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

在從服務(wù)器上查看一下

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

數(shù)據(jù)已經(jīng)同步過來了。在從上創(chuàng)建一個(gè)數(shù)據(jù)庫

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)

然后再主服務(wù)上查看

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

mysql>

數(shù)據(jù)沒有同步過來嫁审，也就是說主不會(huì)同步從的數(shù)據(jù)跋炕。當(dāng)然兩臺(tái)也可以配置互為主備,今天就講到這里了。