第三章 MySQL的多实例

2022-02-22 12:55:27

一、MySQL服务构成

1.MySQL程序结构

1.连接层
2.sql层
3.存储引擎层

2.MySQL逻辑结构

1.库
2.表：元数据+真实数据行
3.元数据：列+其它属性（行数+占用空间大小+权限）
4.列：列名字+数据类型+其他约束（非空、唯一、主键、非负数、自增长、默认值）

MySQL	Linux
库	目录
show databases;	ls-l /
use mysql	cd /mysql
表	文件
show tables;	ls
二维表=元数据+真实数据行	文件=文件名+文件属性

3.MySQL物理结构

1）结构

1.MySQL的最底层的物理结构是数据文件，也就是操作服务器的磁盘
2.存储引擎分为很多种类（Linux中的FS）
3.不同存储引擎的区别：存储方式、安全性、性能

#myisam存储引擎
-rw-rw---- 1 mysql mysql  10684 Oct 19 17:09 user.frm
-rw-rw---- 1 mysql mysql    488 Oct 20 18:28 user.MYD
-rw-rw---- 1 mysql mysql   2048 Oct 20 18:28 user.MYI

#innodb存储引擎
-rw-rw---- 1 mysql mysql   9230 Oct 19 17:09 slave_worker_info.frm
-rw-rw---- 1 mysql mysql  98304 Oct 19 17:09 slave_worker_info.ibd

#注意：建表或者库时名字一定都要是小写

2）MySQL存储物理大小

1.段：理论上一个表就是一个段，由多个区构成，（分区表是一个分区一个段）
2.区：连续的多个页构成
3.页：最小的数据存储单元，默认是16k

4.分区表：一个区构成一个段就是一个表

二、MySQL多实例

1.实例

1.单实例：
	1个进程 + 多个线程 + 单个预分配的内存空间

2.多实例：
	多个进程 + 多个线程 + 多个预分配的内存空间

3.MySQL多实例：
	1）多个端口
	2）多个socket文件
	3）多个server_id
	4）多个日志文件
	5）多个配置文件
	6）多个数据目录

2.创建多实例存放目录

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

3.配置多个配置文件

[root@db02 /service]# vim /service/3307/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3307/data
port=3307
socket=/service/3307/data/mysql.sock
server_id=7
log-error=/service/3307/data/mysql.err

[root@db02 /service]# vim /service/3308/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3308/data
port=3308
socket=/service/3308/data/mysql.sock
server_id=8
log-error=/service/3308/data/mysql.err

[root@db02 /service]# vim /service/3309/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3309/data
port=3309
socket=/service/3309/data/mysql.sock
server_id=9
log-error=/service/3309/data/mysql.err

4.初始化多实例数据目录

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

[root@db02 /service]# tree -L 3 ./
./
├── 3307
│   ├── data
│   │   ├── ibdata1
│   │   ├── ib_logfile0
│   │   ├── ib_logfile1
│   │   ├── mysql
│   │   ├── mysql.err
│   │   ├── performance_schema
│   │   └── test
│   └── my.cnf
├── 3308
│   ├── data
│   │   ├── ibdata1
│   │   ├── ib_logfile0
│   │   ├── ib_logfile1
│   │   ├── mysql
│   │   ├── mysql.err
│   │   ├── performance_schema
│   │   └── test
│   └── my.cnf
├── 3309
│   ├── data
│   │   ├── ibdata1
│   │   ├── ib_logfile0
│   │   ├── ib_logfile1
│   │   ├── mysql
│   │   ├── mysql.err
│   │   ├── performance_schema
│   │   └── test
│   └── my.cnf
├── mysql -> /service/mysql-5.6.42
└── mysql-5.6.42

5.授权

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

6.启动多实例

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

7.验证启动

[root@db02 /service]# netstat -lntp | grep 330
tcp6       0      0 :::3306                 :::*                    LISTEN      24992/mysqld
tcp6       0      0 :::3307                 :::*                    LISTEN      32069/mysqld 
tcp6       0      0 :::3308                 :::*                    LISTEN      32234/mysqld
tcp6       0      0 :::3309                 :::*                    LISTEN      32394/mysqld

8.登录验证多实例

[root@db02 /service]# mysql -uroot -S /service/3307/data/mysql.sock -e ‘show variables like "server_id"‘
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 7     |
+---------------+-------+

[root@db02 /service]# mysql -uroot -S /service/3308/data/mysql.sock -e ‘show variables like "server_id"‘
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 8     |
+---------------+-------+

[root@db02 /service]# mysql -uroot -S /service/3309/data/mysql.sock -e ‘show variables like "server_id"‘
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 9     |
+---------------+-------+

9.多实例设置密码

[root@db02 /service]# mysqladmin -uroot password -S /service/3307/data/mysql.sock
New password: 123
Confirm new password: 123
[root@db02 /service]# mysqladmin -uroot password -S /service/3308/data/mysql.sock
New password: 123
Confirm new password: 123
[root@db02 /service]# mysqladmin -uroot password -S /service/3309/data/mysql.sock
New password: 123
Confirm new password: 123

10.配置简单连接数据库多实例

#1.编写连接数据库文件
[root@db02 /service]# cat /usr/bin/mysql3307
mysql -uroot -p123 -S /service/3307/data/mysql.sock
[root@db02 /service]# cat /usr/bin/mysql3308
mysql -uroot -p123 -S /service/3308/data/mysql.sock
[root@db02 /service]# cat /usr/bin/mysql3309
mysql -uroot -p123 -S /service/3309/data/mysql.sock

#2.授权执行权限
[root@db02 /service]# chmod +x /usr/bin/mysql*

#3.直接使用命令连接
[root@db02 /service]# mysql3307
[root@db02 /service]# mysql3308
[root@db02 /service]# mysql3309

三、数据库多实例主从

0）配置主库的binlog

[root@db02 /service]# vim /service/3307/my.cnf 
... ...
log_bin=/service/3307/data/mysql-bin

1.主库授权主从的用户

mysql> grant replication slave on *.* to rep@‘172.16.1.%‘ identified by ‘123‘;
Query OK, 0 rows affected (0.00 sec)

2.主库查看binlog信息

mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 |      120 |
+------------------+----------+
1 row in set (0.01 sec)

3.从库配置同步主库

#1.从库需要知道主库的信息
	1）主库的地址
	2）主库的端口
	3）主库授权的用户
	4）主库授权的用户的密码
	5）主库的binlog日志名字
	6）binlog的位置点
	
#2.从库配置主从同步
mysql> change master to
    -> master_host=‘127.0.0.1‘, 
    -> master_port=3307,
    -> master_user=‘rep‘,
    -> master_password=‘123‘,
    -> master_log_file=‘mysql-bin.000001‘,
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

change master to
master_host=‘127.0.0.1‘, 
master_port=3307,
master_user=‘rep‘,
master_password=‘123‘,
master_log_file=‘mysql-bin.000001‘,
master_log_pos=120;

4.启动主从同步

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

5.查看主从状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rep
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 416
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 579
        Relay_Master_Log_File: mysql-bin.000001
        
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

6.主从同步问题

1）IO线程为NO

1.检查网络
	[root@db02 ~]# ping 127.0.0.1
2.检查端口
	[root@db02 ~]# telnet 127.0.0.1 3307
3.查看防火墙
4.主从同步的用户和密码错误
	检查change master to语句信息是否正确
5.数据库反向解析

2）SQL线程为NO

1.主库有的数据，从库没有
2.从库有的数据，主库没有
3.主库与从库数据不一致

四.MySQL5.6与MySQL5.7安装的区别

1.cmake的时候加入了bostorg
2.初始化时使用mysqld --initialize 替代 mysql_install_db，其它参数没有变化：--user= --basedir= --datadir=
3.--initialize会生成一个临时密码
4.还可以用另外一个参数--initialize-insecure

[root@db02 mysql-5.7.20]# yum install -y gcc gcc-c++ automake autoconf
[root@db02 mysql-5.7.20]# yum install make cmake bison-devel ncurses-devel libaio-devel
[root@db02 mysql-5.7.20]# wget https://dl.bintray.com/boostorg/release/1.65.1/source/boost_1_59_0.tar.gz
#登录boost.org下载也可以
[root@db02 mysql-5.7.20]# tar xf boost_1_59_0.tar.gz -C /usr/local/
[root@db02 mysql-5.7.20]#
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.7.20 -DMYSQL_DATADIR=/application/mysql-5.7.20/data -DMYSQL_UNIX_ADDR=/application/mysql-5.7.20/tmp/mysql.sock #开启bost库
-DDOWNLOAD_BOOST=1 #指定bost库地址
-DWITH_BOOST=/usr/local/boost_1_59_0 -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ZLIB=bundled -DWITH_SSL=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=1 -DENABLE_DOWNLOADS=1 -DWITH_DEBUG=0

五、MySQL用户权限管理

1.linux用户和mysql用户对比

	linux系统	mysql数据库
用户的作用	1.登录系统 2.管理系统文件、服务	1.登录数据库 2.管理数据库数据
创建用户	1.useradd 2.adduser	1.create user root@‘localhost‘; 2.grant ...
用户密码	1.password lhd 2.useradd lhd -p 12345678	1.create user 2.grant 3.mysqladmin
删除用户	1.userdel -r	1.drop user root@‘localhost‘; 2.delete
修改用户	1.usermod	1.update 2.grant

2.在mysql中，用户是怎么定义的？

#在MySQL中，一个完整的用户是:  ‘用户‘@‘主机域‘

1.用户名定义方法：
	1）如果是数字为用户名，需要加引号
		mysql> create user ‘123‘@‘locahost‘;
	2）如果是字符为用户名，不需要加引号
		mysql> create user lhd@‘locahost‘;
		
2.主机域的定义方式：
	1）localhost
	2）127.0.0.1
	3）db01
	4）::1
	5）%
	6）10.0.0.%  （10.0.0.1-255）
	7）10.0.%.%
	8）10.%.%.%
	9）10.0.0.5%  (10.0.0.50-59)
	10）10.0.0.0/255.255.255.0
	#可以设置，但是不能使用该用户连接
	11）10.0.0.0/24

3.用户管理

1）创建用户并设置密码

mysql> create user lhd@‘172.16.1.%‘ identified by ‘123‘;
Query OK, 0 rows affected (0.02 sec)

2）查看用户

mysql> select user,host from mysql.user;
+--------+------------------------+
| user   | host                   |
+--------+------------------------+
| qiudao | 10.0.0.0/24            |
| lhd    | 10.0.0.0/255.255.255.0 |
| lhd    | 172.16.1.%             |
| root   | localhost              |
+--------+------------------------+
4 rows in set (0.00 sec)

3）删除用户

mysql> drop user qiudao@‘10.0.0.0/24‘;
Query OK, 0 rows affected (0.00 sec)

4）修改用户密码

#1.方式一：命令行修改密码
[root@db02 /service]# mysqladmin -uroot -p password
Enter password: 123456
New password: 123
Confirm new password: 123

#2.方式二：授权的方式修改密码
mysql> grant all on *.* to lhd@‘10.0.0.0/255.255.255.0‘ identified by ‘123456‘;
Query OK, 0 rows affected (0.00 sec)

#3.方式三：更新数据库密码
mysql> update mysql.user set password=PASSWORD(‘123456‘) where user=‘lhd‘ and host=‘172.16.1.%‘;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

#4.方式四：直接设置密码
mysql> set password=PASSWORD(‘123456‘);
Query OK, 0 rows affected (0.00 sec)

5）忘记root密码怎么办

#1.停止数据库
#2.跳过授权表和网络启动
#3.连接数据库
#4.刷新授权表
#5.修改密码
#6.正常启动数据库

4.权限管理

1）权限管理的命令

mysql> grant all on *.* to root@‘172.16.1.51‘ identified by ‘123‘;

grant all on *.* to root@‘172.16.1.51‘ identified by ‘123‘;
grant 				#授权命令
all 				#所有权限
on 					#在。。。上
*.* 				#所有库.（包含）所有表
to 					#给。。
root@‘172.16.1.51‘ 	  #定义的用户  ‘用户名‘@‘主机域‘
identified 			 #设置密码
by 					#是
‘123‘;

2）查看所有权限

#1.回收权限
mysql> revoke select on *.* from root@‘172.16.1.51‘;
Query OK, 0 rows affected (0.00 sec)

#2.查看用户的权限
mysql> show grants for root@‘172.16.1.51‘;

#3.所有权限包括：
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, GRANT

5.授权的对象

mysql> grant all on *.* to root@‘172.16.1.51‘ identified by ‘123‘;   		#所有库所有表
mysql> grant all on mysql.* to root@‘172.16.1.51‘ identified by ‘123‘;		#单库授权
mysql> grant all on mysql.user to root@‘172.16.1.51‘ identified by ‘123‘;	#单表授权

#单列授权，企业中叫做脱敏，脱敏：脱离敏感信息

六、实战演练：

一、需求

1.恢复快照
2.配置多实例数据库
3.做多实例的主从同步
4.做多台服务器的主从同步

二、环境准备

主机名	IP地址	搭建的服务
db01	172.16.1.51	数据库主库
db02	172.16.1.52	数据库从库、数据库多实例主从
db03	172.16.1.53	数据库从库

三、db01配置

1.下载源码包
[root@db01 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz

2.安装依赖
[root@db01 ~]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel

3.解压源码包
[root@db01 ~]# tar xf mysql-5.6.46.tar.gz 

4.生成cmake
[root@db01 ~]# cd mysql-5.6.46/
[root@db01 ~/mysql-5.6.46]# mkdir /service
[root@db01 ~/mysql-5.6.46]# cmake . -DCMAKE_INSTALL_PREFIX=/service/mysql-5.6.46 -DMYSQL_DATADIR=/service/mysql-5.6.46/data -DMYSQL_UNIX_ADDR=/service/mysql-5.6.46/data/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ZLIB=bundled -DWITH_SSL=system -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=1 -DENABLE_DOWNLOADS=1 -DWITH_DEBUG=0

5.编译make
[root@db01 ~/mysql-5.6.46]# make && make install

6.做软连接
[root@db01 ~/mysql-5.6.46]# ln -s /service/mysql-5.6.46 /service/mysql

7.创建用户
[root@db01 ~/mysql-5.6.46]# useradd mysql -s /sbin/nologin -M

8.拷贝启动文件和配置文件
[root@db01 ~/mysql-5.6.46]# cd /service/mysql/support-files/
[root@db01 /service/mysql/support-files]# cp my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
[root@db01 /service/mysql/support-files]# cp mysql.server /etc/init.d/mysqld

9.初始化
[root@db01 /service/mysql/support-files]# cd /service/mysql/scripts/
[root@db01 /service/mysql/scripts]# ./mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data

10.配置system管理启动MySQL
[root@db01 /service/mysql/scripts]#  vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

[root@db01 /service/mysql/scripts]# systemctl daemon-reload

11.修改启动文件
[root@db01 ~]# vim /etc/init.d/mysqld 
basedir=/service/mysql
datadir=/service/mysql/data

12.启动数据库
[root@db01 ~]# systemctl start mysqld

13.设置环境变量
[root@db01 ~]# vim /etc/profile.d/mysql.sh
export PATH=/service/mysql/bin:$PATH

[root@db01 ~]# source /etc/profile

14.设置数据库密码
[root@db01 ~]# mysqladmin -uroot password root

15.修改配置文件
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/mysql/data
port=3306
socket=/service/mysql/data/mysql.sock
server_id=1
log-error=/service/mysql/data/mysql.err
log_bin=/service/mysql/data/mysql-bin

16.重启数据库服务
[root@db01 ~]# systemctl restart mysqld.service 

17.进行数据库用户授权
mysql> grant replication slave on *.* to rep@‘172.16.1.%‘ identified by ‘123‘;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "server_id";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

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

四、db02从库配置

1.下载源码包
[root@db02 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz

2.安装依赖
[root@db02 ~]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel

3.解压源码包
[root@db02 ~]# tar xf mysql-5.6.46.tar.gz 

4.生成cmake
[root@db02 ~]# cd mysql-5.6.46/
[root@db02 ~/mysql-5.6.46]# mkdir /service
[root@db02 ~/mysql-5.6.46]# cmake . -DCMAKE_INSTALL_PREFIX=/service/mysql-5.6.46 -DMYSQL_DATADIR=/service/mysql-5.6.46/data -DMYSQL_UNIX_ADDR=/service/mysql-5.6.46/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ZLIB=bundled -DWITH_SSL=system -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=1 -DENABLE_DOWNLOADS=1 -DWITH_DEBUG=0

5.编译make
[root@db02 ~/mysql-5.6.46]# make && make install

6.做软连接
[root@db02 ~/mysql-5.6.46]# ln -s /service/mysql-5.6.46 /service/mysql

7.创建用户
[root@db02 ~/mysql-5.6.46]# useradd mysql -s /sbin/nologin -M

8.拷贝配置文件和启动文件
[root@db02 ~/mysql-5.6.46]# cd /service/mysql/support-files/
[root@db02 /service/mysql/support-files]# cp my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
[root@db02 /service/mysql/support-files]# cp mysql.server /etc/init.d/mysqld

9.初始化
[root@db02 /service/mysql/support-files]# cd /service/mysql/scripts/
[root@db02 /service/mysql/scripts]# ./mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data

10.配置system管理启动MySQL
[root@db02 /service/mysql/scripts]#  vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

[root@db02 /service/mysql/scripts]# systemctl daemon-reload

11.修改启动文件
[root@db02 ~]# vim /etc/init.d/mysqld 
basedir=/service/mysql
datadir=/service/mysql/data

12.启动数据库
[root@db02 ~]# systemctl start mysqld

13.设置环境变量
[root@db02 ~]# vim /etc/profile.d/mysql.sh
export PATH=/service/mysql/bin:$PATH

[root@db02 ~]# source /etc/profile

14.设置数据库密码
[root@db02 ~]# mysqladmin -uroot password root

15.修改配置文件
[root@db02 ~]# vim /etc/my.cnf
server_id = 2

16.重启数据库服务
[root@db02 ~]# systemctl restart  mysqld.service 

17.从库配置主从同步
mysql> change master to
    -> master_host=‘172.16.1.51‘,
    ->  master_port=3306,
    -> master_user=‘rep‘,
    ->  master_password=‘123‘,
    -> master_log_file=‘mysql-bin.000001‘,
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

18.启动主从同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#查看主从状态
mysql>  show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

五、db02配置多实例主从

1.创建多实例存放目录
[root@db02 ~]# mkdir /service/{3307,3308,3309}

2.配置多个配置文件
[root@db02 /service]# vim /service/3307/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3307/data
port=3307
socket=/service/3307/data/mysql.sock
server_id=7
log-error=/service/3307/data/mysql.err

[root@db02 /service]# vim /service/3308/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3308/data
port=3308
socket=/service/3308/data/mysql.sock
server_id=8
log-error=/service/3308/data/mysql.err

[root@db02 /service]# vim /service/3309/my.cnf
[mysqld]
basedir=/service/mysql
datadir=/service/3309/data
port=3309
socket=/service/3309/data/mysql.sock
server_id=9
log-error=/service/3309/data/mysql.err

3.初始化多实例数据目录
[root@db02 /service]# cd mysql/scripts/
[root@db02 /service/mysql/scripts]# ./mysql_install_db --defaults-file=/service/3307/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3307/data
[root@db02 /service/mysql/scripts]# ./mysql_install_db --defaults-file=/service/3308/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3308/data
[root@db02 /service/mysql/scripts]# ./mysql_install_db --defaults-file=/service/3309/my.cnf --user=mysql --basedir=/service/mysql --datadir=/service/3309/data

4.授权
[root@db02 /service]# chown -R mysql.mysql /service/

5.启动多实例
[root@db02 /service]# mysqld_safe --defaults-file=/service/3307/my.cnf &
[root@db02 /service]# mysqld_safe --defaults-file=/service/3308/my.cnf &
[root@db02 /service]# mysqld_safe --defaults-file=/service/3309/my.cnf &

6.多实例设置密码
[root@db02 /service]# mysqladmin -uroot password -S /service/3307/data/mysql.sock
[root@db02 /service]# mysqladmin -uroot password -S /service/3308/data/mysql.sock
[root@db02 /service]# mysqladmin -uroot password -S /service/3309/data/mysql.sock

7.配置简单连接数据库多实例
#编写连接数据库文件
[root@db02 /service]# cat /usr/bin/mysql3307
mysql -uroot -proot -S /service/3307/data/mysql.sock
[root@db02 /service]# cat /usr/bin/mysql3308
mysql -uroot -proot -S /service/3308/data/mysql.sock
[root@db02 /service]# cat /usr/bin/mysql3309
mysql -uroot -proot -S /service/3309/data/mysql.sock

#授权执行权限
[root@db02 /service]# chmod +x /usr/bin/mysql*

#直接使用命令连接
[root@db02 /service]# mysql3307
[root@db02 /service]# mysql3308
[root@db02 /service]# mysql3309

8.配置主库的binlog
[root@db02 /service]# vim /service/3307/my.cnf 
...
log_bin=/service/3307/data/mysql-bin

9.主库授权主从的用户
mysql> grant replication slave on *.* to rep@‘localhost‘ identified by ‘123‘;
Query OK, 0 rows affected (0.00 sec)

10.重启主库
[root@db02 ~]# mysqld_safe --defaults-file=/service/3307/my.cnf &

#主库查看binlog信息
mysql> show master status;
+------------------+----------+
| File             | Position |
+------------------+----------+
| mysql-bin.000001 |      120 |
+------------------+----------+
1 row in set (0.01 sec)

11.3308从库配置主从同步
mysql> change master to
    -> master_host=‘127.0.0.1‘, 
    -> master_port=3307,
    -> master_user=‘rep‘,
    -> master_password=‘123‘,
    -> master_log_file=‘mysql-bin.000001‘,
    -> master_log_pos=120;

12.3308开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

#查看主从状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rep
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 325
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 488
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

13.3309从库配置主从同步
mysql> change master to
    -> master_host=‘127.0.0.1‘, 
    -> master_port=3307,
    -> master_user=‘rep‘,
    -> master_password=‘123‘,
    -> master_log_file=‘mysql-bin.000001‘,
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.07 sec)

14.3309开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

#查看主从状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: rep
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 325
               Relay_Log_File: db02-relay-bin.000002
                Relay_Log_Pos: 488
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

六、db03配置

1.下载源码包
[root@db03 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz

2.安装依赖
[root@db03 ~]# yum install -y ncurses-devel libaio-devel gcc gcc-c++ glibc cmake autoconf openssl openssl-devel

3.解压源码包
[root@db03 ~]# tar xf mysql-5.6.46.tar.gz 

4.生成cmake
[root@db03 ~]# cd mysql-5.6.46/
[root@db03 ~/mysql-5.6.46]# mkdir /service
[root@db03 ~/mysql-5.6.46]# cmake . -DCMAKE_INSTALL_PREFIX=/service/mysql-5.6.46 -DMYSQL_DATADIR=/service/mysql-5.6.46/data -DMYSQL_UNIX_ADDR=/service/mysql-5.6.46/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITH_ZLIB=bundled -DWITH_SSL=system -DENABLED_LOCAL_INFILE=1 -DWITH_EMBEDDED_SERVER=1 -DENABLE_DOWNLOADS=1 -DWITH_DEBUG=0

5.编译make
[root@db03 ~/mysql-5.6.46]# make && make install

6.做软连接
[root@db03 ~/mysql-5.6.46]# ln -s /service/mysql-5.6.46 /service/mysql

7.创建用户
[root@db03 ~/mysql-5.6.46]# useradd mysql -s /sbin/nologin -M

8.拷贝配置文件和启动文件
[root@db03 ~/mysql-5.6.46]# cd /service/mysql/support-files/
[root@db03 /service/mysql/support-files]# cp my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
[root@db03 /service/mysql/support-files]# cp mysql.server /etc/init.d/mysqld

9.初始化
[root@db03 /service/mysql/support-files]# cd /service/mysql/scripts/
[root@db03 /service/mysql/scripts]# ./mysql_install_db --user=mysql --basedir=/service/mysql --datadir=/service/mysql/data

10.配置system管理启动MySQL
[root@db03 /service/mysql/scripts]#  vim /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=https://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/service/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
LimitNOFILE = 5000

[root@db03/service/mysql/scripts]# systemctl daemon-reload

11.修改启动文件
[root@db03~]# vim /etc/init.d/mysqld 
basedir=/service/mysql
datadir=/service/mysql/data

12.启动数据库
[root@db03~]# systemctl start mysqld

13.设置环境变量
[root@db03~]# vim /etc/profile.d/mysql.sh
export PATH=/service/mysql/bin:$PATH

[root@db03~]# source /etc/profile

14.设置数据库密码
[root@db03~]# mysqladmin -uroot password root

15.修改配置文件
[root@db03 ~]# vim /etc/my.cnf
server_id = 3

16.重启数据库服务
[root@db03 ~]# systemctl restart  mysqld.service 

15.配置主从同步
mysql> change master to
    -> master_host=‘172.16.1.51‘,
    -> master_port=3306,
    -> master_user=‘rep‘,
    -> master_password=‘123‘,
    -> master_log_file=‘mysql-bin.000001‘,
    -> master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

16.开始主从同步
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

#查看主从状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.1.51
                  Master_User: rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 120
               Relay_Log_File: db03-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

第三章 MySQL的多实例

码农公寓

一、MySQL服务构成

1.MySQL程序结构

2.MySQL逻辑结构

3.MySQL物理结构

1）结构

2）MySQL存储物理大小

二、MySQL多实例

1.实例

2.创建多实例存放目录

3.配置多个配置文件

4.初始化多实例数据目录

5.授权

6.启动多实例

7.验证启动

8.登录验证多实例

9.多实例设置密码

10.配置简单连接数据库多实例

三、数据库多实例主从

0）配置主库的binlog

1.主库授权主从的用户

2.主库查看binlog信息

3.从库配置同步主库

4.启动主从同步

5.查看主从状态

6.主从同步问题

1）IO线程为NO

2）SQL线程为NO

四.MySQL5.6与MySQL5.7安装的区别

五、MySQL用户权限管理

1.linux用户和mysql用户对比

2.在mysql中，用户是怎么定义的？

3.用户管理

1）创建用户并设置密码

2）查看用户

3）删除用户

4）修改用户密码

5）忘记root密码怎么办

4.权限管理

1）权限管理的命令

2）查看所有权限

5.授权的对象

六、实战演练：

一、需求

二、环境准备

三、db01配置

四、db02从库配置

五、db02配置多实例主从

六、db03配置

相关文章