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步提出的数据