1.MYSQL的多实例
# mysql 多实例，也是多个配置文件启动数据库
mysql多个配置文件：
1.多个端口
2.多个socket文件
3.多个日志文件
4.多个srver_id
5.多个数据目录
# 实例
1.什么是单实例
一个进程 + 多个线程 + 一个预分配的内存空间
2.多实例
多个进程 + 多个线程 + 多个预分配的内存空间
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*
2.数据库多实例主从复制
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.主从复制的问题
1.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.从库有数据，主库没有
#主从数据库数据不一致