一：常用

#授权root给任何IP，对任何数据库.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
#刷新授权(立刻生效)
flush privileges;
#初始化mysql密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
#更改root密码
update user set Password=password('123456') where User='root'; 
#设置root密码为hzins618
./bin/mysqladmin -u root -p password hzins618
#查看慢查询
select * from information_schema.processlist where command<>'sleep';
#查看编码
show variables like 'character_%';
show variables like 'collation_%';
#登录
语法：mysql -u用户名 -p用户密码
举例：mysql -uroot -p123456
语法：mysql -u用户名 -p+回车，然后输入密码
举例：mysql -uroot -p
#查看所有链接
show full processlist;
#刷新
flush privileges;
#显示用户的授权
SHOW GRANTS FOR user_name;
#从库用户创建，授权
grant REPLICATION SLAVE,REPLICATION CLIENT on *.* to 'repl'@'172.30.211.15' identified by '123456' with grant option;
#建库
CREATE DATABASE `huize_bi` CHARACTER SET 'utf8mb4';
#查看程序运行所需的共享库
ldd `which sshd`
#mysql检查
数据一致性
checksum            mysql中一致性检查命令
mysqldiff           mysql-utilities中对比表结构
pt-table-checksum   主从数据一致性检查
# 查看执行sql状态
show full processlist;    
show processlist;         
# 查看主或者从节点状态
show slave status\G    
show master status\G   
# 查看系统和状态变量
show variables\G      
show status\G
# mysql授权prometheus
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO prometheus@'%' identified by 'sJdf9123i1aZl' ;
# 查看支持的存储引擎
SHOW ENGINES
# 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'
# 查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table tablename
# 准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name="tablename"
# 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE t1 (i INT) ENGINE = INNODB;
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;
#修改存储引擎
ALTER TABLE t ENGINE = InnoDB;
#修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER;
#修改表结构(添加索引)：
ALTER table tableName ADD [UNIQUE] INDEX indexName(columnName)
#查看索引
SHOW INDEX FROM table_name\G
#确定版本和是否开启了 binlog 日志，以及日志格式
show variables like 'binlog_format';
show variables like 'log_bin';
select version();
#按照登录用户+数据库+登录服务器查看登录信息
SELECT 
DB as database_name,
USER as login_user,
LEFT(HOST,POSITION(':' IN HOST)-1) AS login_ip,
count(1) as login_count
FROM `information_schema`.`PROCESSLIST` P 
WHERE P.USER NOT IN('root','repl','system user') 
GROUP BY DB,USER,LEFT(HOST,POSITION(':' IN HOST)-1)
ORDER BY COUNT(1) DESC;

查看mysql连接数

1、MySQL> show status like '%connect%';

   Connections，试图连接到（不管是否成功）MySQL服务器的连接数。
   Max_used_connections，服务器启动后已经同时使用的连接的最大数量。
   Threads_connected，当前的连接数。

2、mysql> show variables like '%connect%';

   max_connections，最大连接数。

3、修改max_connections

   在配置文件（my.cnf或my.ini）在最下面，天加一句：
   max_connections=32000
   然后，用命令重启：/etc/init.d/mysqld restart
   虽然这里写的32000，实际MySQL服务器允许的最大连接数16384；
   添加了最大允许连接数，对系统消耗增加不大
4、临时生效 
 set GLOBAL max_connections=1000;

mysql 客户端安装

#安装yum源
rpm -ivh https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm

#可以通过yum搜索
yum search mysql-community
#安装的是64位
yum install mysql-community-client.x86_64 --nogpgcheck -y

事务的属性

事务具有以下四个标准属性，通常用缩略词 ACID 来表示：

- 原子性：保证任务中的所有操作都执行完毕；否则，事务会在出现错误时终止，并回滚之前所有操作到原始状态。
- 一致性：如果事务成功执行，则数据库的状态得到了进行了正确的转变。
- 隔离性：保证不同的事务相互独立、透明地执行。
- 持久性：即使出现系统故障，之前成功执行的事务的结果也会持久存在。

事务控制

有四个命令用于控制事务：

- COMMIT：提交更改；
- ROLLBACK：回滚更改；
- SAVEPOINT：在事务内部创建一系列可以 ROLLBACK 的还原点；
- SET TRANSACTION：命名事务；

SQL 语句及其种类

#DDL（数据定义语言）
create ==> 创建数据库或者表等对象
drop ==> 删除数据库或者表等对象
alter ==> 修改数据库或者表等对象的结构
#DML（数据操作语言）
select ==> 查询表中数据
insert ==> 向表中插入数据
update ==> 更新表中数据
delete ==> 删除表中数据
#DCL（数据控制语言）
commit ==> 决定对数据库中的数据进行变更
rollback ==> 取消对数据库中的数据进行变更
grant ==> 赋予用户操作权限
revoke ==> 取消用户的操作权限

二：备份与恢复

2.1: 备份与恢复 表

#方法1
create table 备份 like 主表（备份结构）
INSERT INTO 备份表 SELECT * FROM user;（备份数据）

#方法2
备份channel_cpp库的c_trip_renewal表
mysqldump -udba -p -S /tmp/mysql3310.sock channel_cpp c_trip_renewal > /data/backup/20210304.sql

2.2: 备份与恢复 库

#备份库到当前目录
$mysqldump –u root –p*** dbname > filename.sql

#还原库  前提是数据库必须存在
$mysql  –u root –p***　dbname <filename.sql
#或者
mysql>use dbname;

mysql>source (fullpath)filename.sql

mysqldump -udba -p -S /tmp/mysql3311.sock --single-transaction product_travel_ps >/data/mysqlbackup/product_travel_ps_210225.sql

mysqldump -udba -p -S /tmp/mysql3310.sock channel_cpp c_trip_renewal > /data/backup/20210304.sql

 #备份某个数据库
mysqldump -uroot -p  database_name >/tmp/mysql.bak.sql;
#备份数据库中的某张表
mysqldump -uroot -p  database_name table_name >/tmp/mysql.table.sql;
#还原某个数据库
mysql -uroot -p  database_name < /tmp/mysql.bak.sql;
#还原数据库中的某一张表
mysql -uroot -p  database_name < /tmp/mysql.table.sql;

三：记录

查看表容量

#查看mysql数据库容量大小
#第一种情况：查询所有数据库的总大小，方法如下：
mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
+-----------+
| data      |
+-----------+
| 3052.76MB |
+-----------+
1 row in set (0.02 sec)
#统计一下所有库数据量 
每张表数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH
SELECT
SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
FROM information_schema.TABLES 
#统计每个库大小：
SELECT
table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb
FROM information_schema.TABLES group by table_schema;  
# 第二种情况：查看指定数据库的大小，比如说：数据库test，方法如下：
mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='test';
+----------+
| data     |
+----------+
| 142.84MB |
+----------+
1 row in set (0.00 sec)
#查看所有数据库各容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;
#查看所有数据库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;
#查看指定数据库容量大小
#例：查看mysql库容量大小
select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql';　
#查看指定数据库各表容量大小
#例：查看mysql库各表容量大小
select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='mysql'
order by data_length desc, index_length desc;

四：自动安装脚本

#!/bin/bash
#脚本说明部分
cat << EOF
1、请事先下载好MySQL二进制安装包并解压于/usr/local下
2、为二进制包做好软连接，如：
        ln -s /usr/local/mysql-5.7.27-linux-glibc2.12-x86_64/ /usr/local/mysql
3、确保上面两步都操作后，运行脚本完成服务安装和初始化，MySQL监听端口为3306，配置文件为/etc/my.cnf
EOF

#变量设置
mysql_dir=/data/mysql
data_dir=/data/mysql/data
log_dir=/data/mysql/log
echo ""
#初始化数据库（可反复执行，但会清空数据）
read -p "是否进行初始化，初始化将清空已有数据,输入y继续,其它按键退出脚本：" input
if [ $input == "y" ];then
  #创建mysql用户
  id mysql > /dev/null 2>&1
  if [ $? != 0 ];then
    useradd -r -s /sbin/nologin -M mysql && echo "成功创建MySQL用户"
    sleep 1
  else
    echo "MySQL用户已存在，无需创建"
    sleep 1
  fi

#创建数据和日志目录
mkdir -p $log_dir
mkdir -p $data_dir
chown -R mysql.mysql $mysql_dir

#创建数据库配置文件
if [ -e /etc/my.cnf ];then
  echo > /etc/my.cnf
else
  touch /etc/my.cnf
fi

#my.cnf配置文件内容
cat > /etc/my.cnf <<EOF
[mysqld]
basedir = /usr/local/mysql
datadir = $data_dir
port = 3306
socket = /tmp/mysql.sock 
character-set-server = utf8mb4
default-storage-engine = INNODB
skip-name-resolve
server_id = 10
# sql_safe_updates=1
sql_mode =STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

#Log setting
log_error = $log_dir/error.log
slow_query_log = on
slow_query_log_file = $log_dir/slow.log
long_query_time = 3
log_bin = $log_dir/master-bin
log_bin_index = $log_dir/master-bin.index
binlog_format = row
expire_logs_days = 10
max_binlog_size = 1024M
binlog_cache_size = 32768

#双1参数，建议都设置为1提升数据安全
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
# Innodb setting
innodb_buffer_pool_size = 512M 
innodb_flush_method=O_DIRECT 
innodb_log_file_size = 200M 
innodb_file_per_table = 1  
innodb_lock_wait_timeout = 10 
# innodb_buffer_pool_dump_at_shutdown = ON  
# innodb_buffer_pool_filename = ib_buffer_pool
# innodb_buffer_pool_load_at_startup = ON 

[mysql]  #作用于mysql客户端工具如果这里配置了先关选项，使用客户端命令就不需要填写了
socket = /tmp/mysql.sock
prompt= "[\\u@linuxe][\\d]>"  
# tee = "/data/dblog/tee.log"  
no-auto-rehash
max_connections=32000
EOF

#检查是否有3306端口占用
mysql_proc=`netstat -ntulp | grep 3306 | wc -l`
if [ $mysql_proc -gt 0 ];then
  echo "当前MySQL正在运行，请输入MySQL管理密码停止进程"
  /usr/local/mysql/bin/mysqladmin -uroot -p -S /tmp/mysql.sock shutdown && echo "MySQL服务已关闭，开始初始化"
    if [ $? -ne 0 ];then
      echo "密码错误，脚本退出" 
      exit 1
    fi
  else
      echo "没有MySQL进程正在运行，开始进行初始化"
      sleep 1
fi

rm -rf $data_dir/*  && echo "已清空数据目录"
rm -rf $log_dir/*  && echo "已清空日志目录"

#初始化数据库
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf  --initialize --user=mysql
if [ $? -ne 0 ];then
  echo "初始化失败，脚本退出" 
  exit 1
else
  mysql_password=`grep "temporary password" $log_dir/error.log | awk '{print $NF}'`
  echo "初始化成功，当前MySQL临时管理密码：$mysql_password"
fi

#启动服务
  /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf 2>&1 > /dev/null &
  echo "请运行/usr/local/mysql/bin/mysql_secure_installation 进行安全初始化"
else
  echo "脚本已退出"
fi