MySQL常用操作及配置

MySQL库操作

连接数据库

mysql [–h主机地址] [–P端口] –u用户名 –p密码

建库

create database 库名;

create database if not exists 库名 default charset utf8 collate utf8_general_ci; -- 指定字符集

删库

drop database 库名;

查看库列表

show databases;

选择数据库

use 库名;

MySQL表操作

查看表列表

show tables;

查看表字段信息

desc 表名;

插入数据

insert into 表名 （column1, column2, column3） values (value1, value2, value3);

更新表

update 表名 set 字段名=值  [where 字段名=值]

删除表

delete from 表名 [where 字段名=值]

截断表

truncate 表名

MySQL表查询

select * from 表名;    -- 查询表内所字段数据

select  字段1， 字段2 from 表名;   -- 查询指字字段数据

select * from 表名 where  字段1=值1 and 字段1=值2;  -- 条件查询，两个条件同时满足

select * from 表名 where  字段1=值1 or 字段1=值2;  -- 条件查询，两条件满足其一

select * from 表名 where 字段 like ‘%aaa%’;  -- 模糊查询，%为通配符

select * from 表1，表2 where   表1.字段= 表2.字段;   -- 表关联查询

select * from 表1 left join 表2 on 表1.字段=表2.字段;  -- 表关联查询 左连接

select * from 表名  where  字段1 in (值1， 值2);   -- 多值查询1

select * from 表1 where 字段 in（select 字段 from 表2）  -- 多值查询2

select * from 表名 limit 1, 10;  -- 分页查询

select 字段1, 字段2 count(*) from 表名 group by 字段1, 字段2  -- 分组查询

select 字段1, 字段2 from 表名order by 字段1， 字段2 [desc];   -- 排序   asc:正序； desc:倒序

Group by > order by > limit

条件查询where

比较运算符

** =，> , <,>=,<=,<>(不等于),

范围（BETWEEN 和NOT BETWEEN）

** select * from t_esl WHERE rom Between 1 And 13 // 查询价签rom 在1到13的版本，1和13是闭区间

列表（in ，not in）

** select* from t_esl where rom in （1,13）// 返回数据是rom位1和13的两个版本的数据

like 通配 利于模糊查询

** select * from 表名 where 字段名 like 对应值（子串）

** select * from t_esl where esl_id like ‘55%’//返回数据是已55开头的价签ID

** select * from t_esl where esl_id like ‘%55’// 返回数据是已55结尾的价签ID

** select * from t_esl where esl_id like ‘%55%’// 返回数据是包含55价签ID

** select * from t_esl where esl_id like ‘5[^5]%’ 将搜索以数字5 开头，并且第二个字母不是 5 的所有ID

MySQL备份、恢复

mysqldump -u用户名 -p密码 --databases 库名1 库名2 [--single-transaction --flush-logs --master-data=2] > backup.sql

# --single-transaction  dump过程中数据一致性可重复读(REPEATABLE READ)事务隔离级别来保证整个

# --flush-logs  生成新的二进制文件

# --master-data=2  值为2时会在导出文件中有一个CHANGE MASTER TO 语句的SQL comment（注释）来提供同步信息，不填为1;

登录数据库后执行以下命令：

source backup.sql;

数据库锁表

全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行。

flush tables with read lock

解锁的语句也是

unlock tables

MySQL配置文件

Windows下的c:/ProgramData/Mysql57/my.ini 或 linux下 /etc/my.cnf

[mysqld]

port=3306   #数据库连接端口

datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data    #数据存储路径

sql-mode=“STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION“   #数据库模式

innodb_buffer_pool_size=1024M   #数据库缓存

max_allowed_packet=100M   #sql语句长度

server-id=1  #设置server-id,每个数据库的server-id必须唯一

log_bin=/var/lib/mysql/mysql-bin.log  #开启二进制日志，MySQL通过些日志文件进行同步

expire_logs_days=3  #log_bin生成文件的保留时间（天）

MySQL主从配置

从库配置修改

[mysqld]

server-id=2  #设置server-id,每个数据库的server-id必须唯一

执行同步SQL

CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=1402;

// MASTER_HOST=主服务器的ip地址

// MASTER_USER=主服务器中创建的同步账号

// MASTER_PASSWORD=主服务器中创建的同步账号的密码

// MASTER_LOG_FILE=主服务器中二进制文件名，show master status;命令中获取得值_

// MASTER_LOG_POS=主服务器中二进制文件的读写位置，show master status;命令中获取的值_

开启从库同步服务

start slave;

查看同步状态

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了

命令：show slave status\G;

 MySQL从库中断恢复

1.备份数据库

mysqldump -u用户名 -p密码 --databases 库名1  库名2  --single-transaction --flush-logs --master-data=2 > backup.sql

// --single-transaction  可重复读(REPEATABLE READ)事务隔离级别来保证整个dump过程中数据一致性

// --flush-logs  生成新的二进制文件

// --master-data=2  值为2时会在导出文件中有一个CHANGE MASTER TO 语句的SQL comment（注释）来提供同步信息，不填为1;

2.查看导出的文件，搜索关键字“CHANGE MASTER TO”

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=154;

// MASTER_LOG_FILE 为同步log-bin文件的文件;

// MASTER_LOG_POS 为同步位置

3.恢复重库备份

mysql -u用户名 -p密码  #登录从库

source  备份的重启文件.sql     #恢复数据库

reset slave;  #重置slave, 重置前要先关掉slave ( stop slave);

CHANGE MASTER TO MASTER_HOST='192.168.1.1', MASTER_USER='rep', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=154;  #执行同步

start slave;  #开启主从备份

// 执行同步时 master_log_file和master_log_pos为第2步提出的数据