老样子，我们再本地来配置这个主从，没法避免的就是需要两套配置文件，闲话不说了，开始搞起。主的配置文件 /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>
数据没有同步过来，也就是说主不会同步从的数据。当然两台也可以配置互为主备,今天就讲到这里了。
网友评论