Mysql模拟百万数据及docker搭建集群

1.MySQL索引

• 主键索引primary key，一个表只能一个字段有主键索引
• 唯一索引unique key
• 常规索引key/index 关键字设置

#索引相关命令
show index from [table_name]

##### 添加索引
alter table [database].[table.name] add fulltext index [index_name](column) #添加全文索引，mysql不支持
alter table [table_name] add PRIMARY KEY(column) # 添加主键索引，表中定义主键之后不能添加

alter table [table_name] add index [index_name] (col1,col2...) #添加普通索引
alter table [table_name] add unique [index_name] (col1,col2) # 添加唯一索引

create unique index [index_name] on [table_name](col1,col2...) #创建唯一索引
create index [index_name] on [table_name](col) #创建普通索引

##### 删除索引
alter table [table_name]  drop index [index_name] #删除索引
drop index [index_name] on [table_name] #删除索引

2.使用MySQL函数插入100万条数据

show fuction status; #查看数据库用户定义的函数
show create function [function_name]; #查询自定义函数创建语句
drop function [function_name]; #删除自定义函数

#创建模拟数据表
create table `mock`(
	`id` bigint(20) unsigned not null auto_increment,
    `name` varchar(50) default "" comment "用户姓名",
    `email` varchar(50) not null comment "用户邮箱",
    `phone` varchar(20) default "" comment "手机号",
    `gender` tinyint(4) unsigned default "0" comment "性别(0:男;1:女)",
    `password` varchar(100) not null comment "密码",
    `age` tinyint(4) default "0" comment "年龄",
    `create_time` datetime not null,
    `update_time` timestamp not null default current_timestamp on update current_timestamp,
    primary key(`id`)
)engine=innodb default charset=utf8mb4 comment "模拟数据";

#创建函数
delimiter $$
create function mock_data()
returns int 
begin
	declare num int default 1000001;
	declare i int default 1;
	while i < num do
		insert into mock(`name`,`email`,`phone`,`gender`,`password`,`age`,`create_time`)values
(concat("用户",i),concat(floor(rand()*999999999),"@qq.com"),concat("18",floor(100000000+rand()*(999999999-100000000))),floor(rand()*2),floor(rand()*999999),floor(18+rand()*42),current_timestamp);
		set i = i + 1;
	end while;
	return i;
end;
delimiter $$
#注意点：代码复制过期，在窗口肯会没有空格，导致报错，mysql版本不同，可能回出现不同的提示，我是用windows管理员窗口执行的
#执行完成之后，可以在其他窗口查询时候有函数生成，有可以执行使用select mock_data()插入数据
select mock_data();

可以通过查询里面的某条数据来验证索引的效果，可以发现，速度明显提升。

3.explain查询执行sql

1. id列：select查询的序列号，有几个select就有几个id。
2. select_type列：表示对应的查询时简单查询(SIMPLE)还是复杂查询(PRIMARY复杂查询的最外层语句，SUBQUERY子查询，不在from中，DERIVED在from子句中的子查询，UNION在union中的第二个和后面的select，UNION RESULT合并的结果)。
3. table列：当前查询访问的哪张表，当时from中的子查询时，table的格式为<derived{id}>,到那时union查询时，为<unionid,id,id>。
4. type列：表述数据扫描类型。(ALL全表扫描，index索引全扫描，range索引范围扫描，ref非唯一索引扫描，eq_ref唯一索引扫描，const结果只有一条的主键索引扫描，system，null表示优化阶段分解查询语句，执行阶段不访问表或者索引)。
5. possible_keys列：字段表示可能用到的索引，为null则表示没有用到索引。
6. key列：字段表示实际用的索引，如果为possible_keys不为null，而key为null，这种情况可能时表中数据不多，mysql没有使用索引而选择了全表扫描。可以在查询使用forde index_name强制使用索引。
7. key_len列：表示索引里使用的字节数。
8. ref列：表示key列中记录的索引中使用到的列或者常量，有const、字段名。
9. rows列：表示扫描的数据行数。
10. Extra列：额外信息，Using index使用了覆盖索引、Using where使用where语句来处理结果，并且查询的列未被索引覆盖、Using index condition查询的列不完全被索引覆盖、Using temporaryMySQL临时创建一张临时表来处理查询、Using filesort使用外部排序而不是索引排序、Select tables optimized away使用某些聚合函数来访问存在索引的某个字段。

4.命令行权限管理和备份

4.1权限管理

#创建用户
create user [user_name] identified by "[password]";
eg:create user admin identified by "123456"

#修改当前用户密码
set password=password("password") #修改当前用户密码
eg:set password=password("1111") #修改当前用户密码为1111

--修改指定用户密码
set password for [user_name]=password("[password]");
set password for admin=password("1111"); #修改admin用户密码为1111

--重命名
rename user [user_name] to [new_user_name];
rename user admin to hello; #修改admin用户名为hello

#授予全部的权限给用户--注意，没有grant权限
grant all privileges on *.* to [user_name] #授予所有权限给用户
grant all privileges on *.* to admin #授予admin用户所有权限 

#查看权限
show grants for [user_name];
show grants for root;

show grants for root@"%";
show grants for root@localhost;

#撤销权限 哪些权限，在哪些库，给谁撤销
revoke 权限 on 库 from 用户;
revoke all privileges on *.* from admin; #撤销用户admin所有库所有权限

#删除用户
drop user [user_name];
drop user admin;

4.2数据备份

导出数据

• 导出物理文件地址
• 可视化工具手动导出
• 使用mysqldump命令行导出，mysqldump -h[ip] -u[root] -p[password] [database] [table1][table2]>D:\sql.sql

mysqldump -hlocalhost -uroot -p1111 test mock>D:\test.sql

导入数据

source sql文件
mysql -u[user_name] -p[password] [database]<sql文件

5.集群搭建

5.1docker搭建集群

docker pull mysql:5.7
docker run -d -p 3306:3306 --name mysql -e MYSQL_ROOT_PASSWORD=1024 mysql:5.7
docker exec -it mysql /bin/bash
docker exec -it mysql mysql -uroot -p1024 #可以直接连接容器内的mysql数据库
/etc/mysql/ #目录下存放的是mysql的配置文件，再给数据库建表之后，生效的配置文件时/etc/mysql/*.cnd来覆盖配置
/var/lib/ #目录会出现三个带有mysql的目录，数据在/var/lib/mysql中

#主机配置
#主机授权slave服务器同步权限
grant replication slave on *.* to 'root'@'从机库ip' identified by 'oycm1234';
grant replication slave on *.* to 'root'@'106.15.234.93' identified by 'oycm1234'
revoke all on *.* from 'root'@'ip'
flush privileges;
show master status; #查询主节点状态

#主机配置文件/etc/my.cnf
[mysqld]
#server-id要唯一
server-id=1
#开启二级制日志功能
log-bin=mysql-bin

#可选配置
#默认是0，表示读写主机。1表示只读从机
read-only=1
#表示日志文件有效时间，6000秒
binlog_expire_logs_seconds=6000
#事务内存大小
binlog_cache_size=1M
#控制单个日志文件的大小，默认和最大时1GB
max_binlog_size=200MB
#设置不需要复制的库
binlog-ingore-db=mysql
#设置需要复制的库，默认全部数据库记录
binlog-do-db=
#binlog格式(mixed,statement,row,默认statement)
binlog_format=statement
#二进制日志过期时间，默认值时0，表示不自动清理
expire_logs_days=7
#跳过复制过程遇到的错误，避免从节点端复制终端，1062指一些主键重复，1032主从数据库数据不一致
slave_skip_errors=1062

SELECT @@server_id;
show variables like 'server_id';
select * from information_schema.processlist as p where p.command = 'Binlog Dump';#查看master的slave

主机配置文件：/etc/my.cnf

[mysqld]
server_id=3307
binlog-ignore-db=mysql
log-bin=mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062

从机配置：/etc/my.cnf

[mysqld]
server_id=3308
binlog-ignore-db=mysql
log-bin=mysql-s1-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=mysql-s1-relay-bin
log_slave_updates=1
read_only=1

5.2docker启动容器

5.2.1运行主服务器

#启动一个主数据库容器
docker run -p 3307:3306 --name mysql-master \
-v /root/mysql-m1/log:/var/log/mysql \
-v /root/mysql-m1/data:/var/lib/mysql \
-v /root/mysql-m1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=1024 \
-d mysql:5.7
#进入主数据库容器，连接服务端，执行从机授权复制
docker exec -it mysql-master /bin/bash
grant replication slave on *.* to 'root'@'106.15.234.93' identified by 'oycm1234';
flush privileges;
show master status;

grant all on *.* to 'root'@'%' identified by '密码' with grant option

5.2.2运行一个从服务器

#启动一个从数据库容器
docker run -p 3308:3306 --name mysql-s1 \
-v /root/mysql-s1:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=1024 \
-d mysql:5.7

docker stop mysql-s1
#添加配置文件重启
docker restart mysql-s1
docker exec -it mysql-s1 /bin/bash

#绑定关系
change master to master_host='106.15.234.93',master_user='root',master_password='oycm1234',master_port=3307,master_log_file='mysql-bin.000004',master_log_pos=2180;

#master_password填写授权时填写的密码

start slave;

show slave status \G;

5.2.3运行另一个从服务器

docker run -p 3309:3306 --name mysql-s2 \
-v /root/mysql-s2:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=1024 \
-d mysql:5.7

docker stop mysql-s2

vim /root/mysql-s2/my.cnf

[mysqld]
server_id=3309
binlog-ignore-db=mysql
log-bin=mysql-s1-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=mysql-s1-relay-bin
log_slave_updates=1
read_only=1

docker restart mysql-s2
docker exec -it mysql-s2 /bin/bash

mysql -uroot -p1024
change master to master_host='106.15.234.93',master_user='root',master_password='oycm1234',master_port=3307,master_log_file='mysql-bin.000003 ',master_log_pos=805;

start slave;

因为数据库是在连接一个从节点后创建的所以有一个数据库会有这个库的记录，另外一个数据库从节点只配置不复制数据库，在和主数据库建立连接之后没有把之前的数据库拷贝过来，所以导致数据缺失。才会出现连接问题。当主库删除从库没有的数据时，会导致，连接出现问题，无法实现数据同步，连接断开。

6.有趣知识

select concat(round(sum(data_length/1024/1024),2),"MB") from information_schema.tables where table_schema="test"; #查询数据test的内存
select concat(round(data_length),"kb") from information_schema.tables where table_schema="test"；
optimize table app_user;优化表
round(数值，几位小数)函数