MySQL配置主从数据库

1. 下载免安装版MySQL，本次演示下载的是mysql-5.7.31-winx64

2. 复制两份，mysql-5.7.31-winx64-1，mysql-5.7.31-winx64-2，并分别创建my.ini(在windows下)

my.ini内容示例：

[mysqld]
basedir = E:\mysql\mysql-5.7.31-winx64-1
datadir = E:\mysql\mysql-5.7.31-winx64-1\data
port = 3316
character-set-server = utf8mb4

server_id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
log_bin=mysql-bin
binlog-format=Row
 
#performance_schema_max_table_instances = 600
#table_definition_cache = 400
#table_open_cache = 256
 
[mysql]
default-character-set = utf8mb4
 
[client]
default-character-set = utf8mb4

3. 创建mysql

第一个mysql
进入E:\mysql\mysql-5.7.31-winx64-1\bin，管理员权限打开cmd，执行命令

mysqld --initialize --console

注意，如果本机已经存在有MySQL实例，mysqld命令可能会调用之前的MySQL，所以可以使用以下命令，后面的mysql install也是一样

.\mysqld --initialize --console

控制台会打印出密码，请记住初始化密码，如果忘记把根目录data文件夹删除，重新执行上面语句。
继续在C:\MySQL\mysql1\bin路径里执行创建服务命令，将服务命名为mysql1：

mysqld --install mysql1

启用服务：

net start mysql1

登录mysql：

mysql -h127.0.0.1 -P3316 -uroot -p
输入初始密码

更改密码为root：

alter user 'root'@'localhost' identified with mysql_native_password by 'root';

第二个mysql
和上面操作一样，服务名为mysql2

4. 设置主从配置

配置主节点，设置从数据库拉取数据的账号

mysql命令登录到主节点：mysql -uroot -hlocalhost -P3316 -p

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.11 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.12 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从节点

注意：MASTER_LOG_POS的值为主数据库中show master status查看到的Position值；
mysql命令登录到从节点：mysql -uroot -hlocalhost -P3326 -p

CHANGE MASTER TO
    MASTER_HOST='localhost',  
    MASTER_PORT = 3316,
    MASTER_USER='repl',      
    MASTER_PASSWORD='123456',   
    MASTER_LOG_FILE='mysql-bin.000002',
    MASTER_LOG_POS=747;
    
    //MASTER_AUTO_POSITION = 1;

开启从数据库线程，并查看状态,确保slave_IO线程和slave_SQL线程都启动

start slave;
show slave status\G

验证操作

在主库执行sql并查看从库数据

mysql> create database db;
Query OK, 1 row affected (0.04 sec)
mysql> use db
Database changed
mysql> create table t1(id int);
Query OK, 0 rows affected (0.17 sec)
mysql>
mysql>
mysql> insert into t1(id) values(1),(2);
Query OK, 2 rows affected (0.04 sec)

注意事项

如果slave_IO线程没起来，可以查看两个数据库的service_id是否冲突，或者从库设置的MASTER_LOG_POS值是否从master查看到的值开始

小知识

1. 主从复制流程：主库监测到insert，delete，alter等改变数据或结构的语句时，会把数据变化写到binlog文件中，然后由从库的slave_IO线程拉取数据写入到从库的relaylog中，在由slave_SQL线程写入从库中。
2. my.ini中的log_bin表示binlog文件的前缀，binlog-format为binlog中的数据格式，均为二进制写入
   binlog-format格式：
   ROW ：
   每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
   Statement：
   不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。
   Mixed：
   以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

参考资料：
https://www.cnblogs.com/huangchuxian/p/10463241.html
https://www.cnblogs.com/langtianya/p/5504774.html