一.mysql结构介绍
1.客户端与服务端模型
1.数据是一个典型的C/S结构的服务
mysql自带的客户端工具
mysql
mysqladmin
mysqldump
mysql是一个二进制程序，后台守护进程
单进程
多线程
2.mysql的连接方式
#1.TCP/IP连接
#2.socket连接
#tcp/ip连接
mysql -uroot -p -h127.0.0.1
mysql -uroot -p -h127.0.0.1 -S /tmp/mysql.sock
#socket连接
mysql -uroot -p -hlocalhost
mysql -uroot -p
#注意：
1）不一定-h就是TCP/IP连接，如果是localhosts就是socket连接
2）socket连接比TCP/IP连接快，TCP/IP连接需要经过三次握手，四次挥手，慢
3）socket连接只能本地连接，远程不能使用
2.实例
#1.什么是单实例?
一个进程 + 多个线程 + 一个预分配的内存空间
#2.什么是多实例?
多个进程 + 多个线程 + 多个预分配的内存空间
3.mysql程序结构
1）连接层
1.验证用户身份，判断用户名和密码
2.提供两种连接方式
3.与SQL层建立交互的线程
2）sql层
1.接收连接层传过来的数据
2.判断sql语句的语法
3.判断SQL语句的语义
4.解析器：解析sql语句，生成执行计划
5.优化器：选出执行计划中最优的计划
6.执行器：执行计划
6.1 与存储引擎层建立交互
6.2 将优化后要执行的SQL发给存储引擎层
7.记录缓存，如果前端有redis，将数据缓存至redis
8.记录日志
3）存储引擎层
1.与sql层建立交互，接收SQL层传来的SQL语句
2.与磁盘进行交互，获取数据，将数据返回给sql层
4.mysql逻辑结构
1.库：库的下面全是表
2.表：元数据 + 真实数据行
3.元数据：列 + 其它属性（行数+占用空间大小+权限）
4.列：列名字 + 数据类型 + 其他约束（非空、唯一、主键、非负数、自增长、默认值）
5.mysql物理结构
1.MySQL的最底层的物理结构是数据文件，也就是说，存储引擎层，打交道的文件，是数据文件。
2.存储引擎分为很多种类（Linux中的FS）
3.不同存储引擎的区别：存储方式、安全性、性能
#数据库创建时，不论库名还是表名，尽量都用小写字母
6.mysql物理大小
1.段：理论上一个表就是一个段，由多个区构成，（分区表是一个分区一个段）
2.区：连续的多个页构成
3.页：最小的数据存储单元，默认是16k
4.分区表：一个区构成一张段，就是一张表
二.mysql多实例
#mysql 多实例，也是多个配置文件启动数据库
mysql多个配置文件：
1.多个端口
2.多个socket文件
3.多个日志文件
4.多个srver_id
5.多个数据目录
1.创建多实例目录
[root@db03 local]# mkdir /usr/local/{3307,3308,3309}
2.编辑配置文件
[root@db03 local]# vim /usr/local/3307/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/3307/data
port=3307
socket=/usr/local/3307/mysql.sock
server_id=1
log_err=/usr/local/3307/data/mysql.err
log_bin=/usr/local/3307/data/mysql-bin
skip-name-resolve
[root@db03 local]# vim /usr/local/3308/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/3308/data
port=3308
socket=/usr/local/3308/mysql.sock
server_id=2
log_err=/usr/local/3308/data/mysql.err
log_bin=/usr/local/3308/data/mysql-bin
skip-name-resolve
[root@db03 local]# vim /usr/local/3309/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/3309/data
port=3309
socket=/usr/local/3309/mysql.sock
server_id=3
log_err=/usr/local/3309/data/mysql.err
log_bin=/usr/local/3309/data/mysql-bin
skip-name-resolve
3.初始化多实例数据目录
[root@db03 local]# cd /usr/local/mysql/scripts/
#3307
[root@db03 scripts]# ./mysql_install_db --defaults-file=/usr/local/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/3307/data
#3308
[root@db03 scripts]# ./mysql_install_db --defaults-file=/usr/local/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/3308/data
#3309
[root@db03 scripts]# ./mysql_install_db --defaults-file=/usr/local/3309/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/3309/data
4.授权目录
[root@db03 scripts]# cd /usr/local/
[root@db03 local]# chown -R mysql.mysql /usr/local/330*
5.启动多实例
#关闭mysql（3306）
[root@db03 scripts]# systemctl stop mysqld
[root@db03 local]# cd /usr/local/mysql/scripts/
#开启
[root@db03 scripts]# mysqld_safe --defaults-file=/usr/local/3307/my.cnf &
[root@db03 scripts]# mysqld_safe --defaults-file=/usr/local/3308/my.cnf &
[root@db03 scripts]# mysqld_safe --defaults-file=/usr/local/3309/my.cnf &
#查看是否启动（有的机器能启三个，有的可能有四个，所以关闭3306的mysql）
[root@db03 scripts]# netstat -lntp
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:111 0.0.0.0:* LISTEN 1/systemd
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 6680/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 6763/master
tcp6 0 0 :::3307 :::* LISTEN 8628/mysqld
tcp6 0 0 :::3308 :::* LISTEN 8920/mysqld
tcp6 0 0 :::3309 :::* LISTEN 9283/mysqld
tcp6 0 0 :::111 :::* LISTEN 1/systemd
tcp6 0 0 :::22 :::* LISTEN 6680/sshd
tcp6 0 0 ::1:25 :::* LISTEN 6763/master
6.连接多实例并验证
[root@db03 scripts]# mysql -S /usr/local/3307/mysql.sock -e "show variables like ‘server_id‘"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
[root@db03 scripts]# mysql -S /usr/local/3308/mysql.sock -e "show variables like ‘server_id‘"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
[root@db03 scripts]# mysql -S /usr/local/3309/mysql.sock -e "show variables like ‘server_id‘"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 3 |
+---------------+-------+
7.mysql多实例设置密码
[root@db03 scripts]# mysqladmin -S /usr/local/3307/mysql.sock -uroot password ‘123‘
Warning: Using a password on the command line interface can be insecure.
[root@db03 scripts]# mysqladmin -S /usr/local/3308/mysql.sock -uroot password ‘123‘
Warning: Using a password on the command line interface can be insecure.
[root@db03 scripts]# mysqladmin -S /usr/local/3309/mysql.sock -uroot password ‘123‘
Warning: Using a password on the command line interface can be insecure.
8.设置密码后连接
[root@db03 scripts]# mysql -S /usr/local/3307/mysql.sock -uroot -p
Enter password:
#可以登陆
#1.弄一个简单的连接方式
[root@db03 scripts]# vim /usr/bin/mysql3307
mysql -S /usr/local/3307/mysql.sock -uroot -p
[root@db03 scripts]# vim /usr/bin/mysql3308
mysql -S /usr/local/3308/mysql.sock -uroot -p
[root@db03 scripts]# vim /usr/bin/mysql3309
mysql -S /usr/local/3309/mysql.sock -uroot -p
#2.授权
[root@db03 scripts]# chmod +x /usr/bin/mysql33*
#3.试试能不能用
[root@db03 scripts]# mysql3307
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.46-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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>
#
三.数据库多实例实现主从复制
1.主库操作
1.开启binlog（前面已经写好）
[root@db03 scripts]# vim /usr/local/3307/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/3307/data
port=3307
socket=/usr/local/3307/mysql.sock
server_id=1
log_err=/usr/local/3307/data/mysql.err
log_bin=/usr/local/3307/data/mysql-bin
skip-name-resolve
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 | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 468 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)
2.从库的操作
#从库需要知道的主库信息
主库的IP
连接主库用的用户和密码
binlog信息
1.登陆数据库，配置主从
#3308和3309都要执行操作
mysql> change master to
-> master_host=‘172.16.1.53‘,
-> master_user=‘rep‘,
-> master_password=‘123‘,
-> master_log_file=‘mysql-bin.000003‘,
-> master_log_pos=468,
-> master_port=3307;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
2.开启主从复制
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
3.查看主从是否成功
#3308,3309都执行，IO，SQL都是yes就说明成功
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.1.53
Master_User: rep
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 468
Relay_Log_File: db03-relay-bin.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 468
Relay_Log_Space: 455
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: a5c9a5c5-c2b0-11ea-9dbb-000c29354aad
Master_Info_File: /usr/local/3308/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
4.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
5.SQL线程不为YES
1.主库有数据，从库没有
2.从库有数据，主库没有
#主从数据库数据不一致
#解决方法：把主库的数据全部导出来，放到从库里去，保证主从库数据一致，再做一次主从复制
MySQL结构介绍及多实例主从复制