mysql多实例和主从区别_MySQL多实例及多实例主从

总结：

· 主从同步依靠IO线程拿取数据，sql线程执行

· 主从同步之前需要做到主从数据库数据一致，再进行同步操作，可以先对主的数据库导出再进行同步，因为从只会同步主的主从后的数据！！！

· 主从同步顺序： 主库锁表--查看FILE及Position的值--导出数据到从库--从库同步主库--解锁主库

一、mysql的多实例

#mysql 多实例，也是多个配置文件启动数据库

mysql多个配置文件：

1.多个端口

2.多个socket文件

3.多个日志文件

4.多个srver_id

5.多个数据目录

1.创建多实例目录

[root@db03 ~]# mkdir /service/{3307,3308,3309}

2.编辑配置文件

[root@db03 ~]# vim /service/3307/my.cnf

[mysqld]

basedir = /service/mysql

datadir = /service/3307/data

port=3307

socket=/service/3307/mysql.sock

server_id=1

log_err=/service/3307/data/mysql.err

log_bin=/service/3307/data/mysql-bin

[root@db03 ~]# vim /service/3308/my.cnf

[mysqld]

basedir = /service/mysql

datadir = /service/3308/data

port=3308

socket=/service/3308/mysql.sock

server_id=2

log_err=/service/3308/data/mysql.err

log_bin=/service/3308/data/mysql-bin

[root@db03 ~]# vim /service/3309/my.cnf

[mysqld]

basedir = /service/mysql

datadir = /service/3309/data

port=3309

socket=/service/3309/mysql.sock

server_id=3

log_err=/service/3309/data/mysql.err

log_bin=/service/3309/data/mysql-bin

3.初识化多实例数据目录

[root@db03 scripts]# ./mysql_install_db --defaults-file=/service/3307/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3307/data

[root@db03 scripts]# ./mysql_install_db --defaults-file=/service/3308/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3308/data

[root@db03 scripts]# ./mysql_install_db --defaults-file=/service/3309/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3309/data

4.授权目录

[root@db03 service]# chown -R mysql.mysql /service/

5.启动多实例

[root@db03 service]# mysqld_safe --defaults-file=/service/3309/my.cnf &

[root@db03 service]# mysqld_safe --defaults-file=/service/3308/my.cnf &

[root@db03 service]# mysqld_safe --defaults-file=/service/3307/my.cnf &

6.连接多实例并验证

[root@db03 service]# mysql -S /service/3308/mysql.sock -e "show variables like 'server_id'"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 2 |

+---------------+-------+

[root@db03 service]# mysql -S /service/3309/mysql.sock -e "show variables like 'server_id'"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 3 |

+---------------+-------+

[root@db03 service]# mysql -S /service/3307/mysql.sock -e "show variables like 'server_id'"

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 1 |

+---------------+-------+

7.mysql多实例设置密码

[root@db03 service]# mysqladmin -S /service/3307/mysql.sock -uroot password '123'

Warning: Using a password on the command line interface can be insecure.

[root@db03 service]# mysqladmin -S /service/3308/mysql.sock -uroot password '123'

Warning: Using a password on the command line interface can be insecure.

[root@db03 service]# mysqladmin -S /service/3309/mysql.sock -uroot password '123'

Warning: Using a password on the command line interface can be insecure.

8.设置密码后连接

[root@db03 service]# mysql -S /service/3307/mysql.sock -uroot -p

Enter password:

#简单连接方式

[root@db03 service]# cat /usr/bin/mysql3307

mysql -S /service/3307/mysql.sock -uroot -p123

[root@db03 service]# cat /usr/bin/mysql3308

mysql -S /service/3308/mysql.sock -uroot -p123

[root@db03 service]# cat /usr/bin/mysql3309

mysql -S /service/3309/mysql.sock -uroot -p123

[root@db03 service]# chmod +x /usr/bin/mysql33*

二、数据库多实例主从复制

1.主库操作

1.开启binlog

[root@db03 service]# vim /service/3307/my.cnf

[mysqld]

basedir = /service/mysql

datadir = /service/3307/data

port=3307

socket=/service/3307/mysql.sock

server_id=1

log_err=/service/3307/data/mysql.err

log_bin=/service/3307/data/mysql-bin

2.主库授权一个用户，给从库用来连接

mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by '123';

Query OK, 0 rows affected (0.18 sec)

3.主库查看binlog信息

mysql> show master status;

+------------------+----------+

| File | Position |

+------------------+----------+

| mysql-bin.000004 | 120 |

+------------------+----------+

1 row in set (0.00 sec)

2.从库的操作

#从库需要知道的主库信息

主库的IP

连接主库用的用户和密码

binlog信息

1.配置主从

change master to

master_host='172.16.1.53',

master_user='rep',

master_password='123',

master_log_file='mysql-bin.000004',

master_log_pos=120,

master_port=3307;

1.开启IO线程和sql线程

mysql> start slave;

Query OK, 0 rows affected (0.08 sec)

3.IO线程不为YES

1.测试网络

[root@db03 ~]# ping 172.16.1.53

2.测试端口

[root@db03 ~]# telnet 172.16.1.53 3307

3.测试主库授权的用户登录

[root@db03 ~]# mysql -urep -p123 -h172.16.1.53 -P3307

4.反向解析

[root@db03 service]# vim /service/3307/my.cnf

skip-name-resolve

5.UUID相同

[root@db03 ~]# cat /service/3307/data/auto.cnf

[auto]

server-uuid=527f6221-c286-11ea-9ca7-000c29e19d84

4.SQL线程不为YES

1.主库有数据，从库没有

2.从库有数据，主库没有

#主从数据库数据不一致