mysql查询字典

ncurses-devel libaio-devel
多实例
启动文件 --data
配置文件 --my.cnf
启动文件 --mysql 自己写个mysql的启动脚本

单实例mysql启动
pkill mysqld
ps -ef|grep mysqld
/etc/init.d/mysqld

/etc/my.cnf 默认去找/etc/my.cnf文件

第一次启动多实例启动
mysqld_safe --defaults-file=/data/3306/my.cnf 2>&1 >/dev/null &
mysqld_safe --defaults-file=/data/3307/my.cnf 2>&1 >/dev/null &

多实例停止
mysqladmin -u root -p123456 -S /data/3306/mysql.sock shutdown
mysqladmin -u root -p123456 -S /data/3307/mysql.sock shutdown

find /data/ type f -name “mysql” |xargs chmod +x
find /data/ type f -name “mysql” -exec ls -l {} \

cp mysql/Path/mysql/bin/* /usr/local/sbin/

初始化路径
5.1 在mysql/bin下
5.5 在 mysql/scripts下
初始化
./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
启动数据库
/data/3306/mysql start #数据库启动脚本 start
/data/3306/mysql start
ss -lutup|grep 330

授权
chown -R mysql.mysql /data/

查看安装路径、配置文件路径等
ps -ef|grep mysql

强制linux不记录敏感历史命令
#HISTCONTROL=ignorespace

修改root登录后提示
放在客户端
[mysql]
prompt /u@test_mysql /r:/m:/s->
单独执行，临时生效
prompt /u@test_mysql /r:/m:/s->

多实例本地登录
mysql -uroot -p -S /data/3306/mysql.sock
mysql -uroot -p -S /data/3307/mysql.sock
多实例远程登录
mysql -uroot -p -h ip -P 3306

mysql 帮助,登录mysql后 help+命令
mysql> help show grant
mysql> help show show

安全
删除所有mysql中的用户，包括root
mysql>delete from mysql.user
新增和root等价的用户
mysql>grant all privileges on . to system@localhost identified by ‘passwd123456’ with grant option;

更改密码
无密码创建密码
#mysqladmin -uroot password ‘创建密码’
多实例 已有密码，修改密码
#mysqladmin -uroot -p’23456’ password’新密码’ -S /data/3306/mysql.sock
mysql>select user,host,password from mysql.user;
mysql>updata mysql.user set password=password(123456) where user=‘root’ and host=‘localhost’;
mysql>flush privileges;
修改当前用户密码
mysql>set password=password(123456);

单实例mysql数据库密码丢失
/etc/init.d/mysqld stop
mysqld_safe --skip-grant-tables --user=mysql &
mysql -uroot -p 空密码登录
mysql>updata mysql.user set password=password(" 123456" ) where user=‘root’ and host=‘localhost’;
mysql>flush privileges;
mysql>quit
mysqladmin -uroot -p’123456’ shutdown
/etc/init.d/mysqld start
mysql -uroot -p

多实例mysqll数据库密码丢失
pkill mysqld
mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables &
mysql -uroot -p -S /data/3306/mysql.sock 空密码登录
mysql>updata mysql.user set password=password(" 123456" ) where user=‘root’ and host=‘localhost’;
mysql>flush privileges;
mysql>quit
mysqladmin -uroot -p’123456’ -S /data/3306/mysql.sock shutdown
/data/3306/mysql start
mysql -uroot -p -S /data/3306/mysql.sock

–skip-grant-tables 密码丢失重设密码启动数据库修改

更改数据库字符集
create database test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
create database test DEFAULT CHARACTER SET gbk COLLATE gbk_general_ci;

编译数据库时指定字符集
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
数据库需要支持所编译的字符集
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii\

查看数据库
mysql>help show
show databases like ‘%sc_%’;
select database(); 查看当前数据库
扩展
select user(); 查看当前用户
select now(); 查看当前时间
select version() 查看当前版本
删除数据库
drop database 数据库名；

删除mysql系统多余账号
drop user “user”@"主机域“ 单引号或者双引号 不能不加
drop user ‘’@‘localhost’; ''空
drop删除不了的(如，主机名大写 特殊字符等)用delete删
delete from mysql.user where user=‘root’ and host=‘oldboy’;
flush privileges;

授权
grant all on test.to ‘test1’@‘localhost’ IDENTIFIED BY ‘123456’;
等价于
create user ‘test1’@‘localhost’ IDENTIFIED BY ‘123456’;
GRANT ALL on test. to ‘test1’@‘localhost’;
show grants for 'test@‘localhost’;

表的操作
show create tables tab1\G
create tab1(
id int(4) not null AUTO_INCREMENT，
name char(20) not null ，
age tinyint(2) NOT NULL default ‘0’ ,
dept varchar(16) default NULL,
列6 类型4 ，
primary key(id),
KEY index_name(name),
) ENGINE=innodb default charset-utf8

查看表结构
desc tab1

创建主键索引
alter table student change id id int primary key auto_increment;

删除主键索引
alter table student drop primary key;

删除普通索引
alter table student drop index index_name;
或者
drop index index_dept on student;

添加索引
alter table student add index index_name(name);

列的前几个字符创建索引
create index index_dept on student(dept(8));

创建联合索引
create index ind_name_dept on student(name,dept);

创建联合索引（前n个字符）
create index ind_name_dept on student(name(4),dept(8));

查看索引
show index from student\G
Non_unique:0 表示唯一
Non_unique:1 表示不唯一索引

索引的前缀生效太特性
index（a,b,c)仅 a,ab,abc三个查询条件生效 其他不生效 比如bc, b等

创建唯一索引(非主键）
create unique index uni_ind_name on student(name);

索引条件
读多写少
在条件列上创建
select 列1，列2 ，列3 from student where 列4 # 在列4上创建索引，而不是在列1,2,3上
唯一值多的大表上

help alter table 或者 help create index 查看索引帮助

插入
insert into student values(每行依次列的值),(行2列1值 ，行2列2值，行2列3值),(),(),();
insert into student(列1，列2，列3）values(列1值,列2值,列3值);

备份数据库
#mysqldump -uroot -p123456 -A >/data/back.sql
#mysqldump -uroot -p123456 -B PRO >/data/pro.sql
#cat /data/back.sql

查询
select id,name from test limit=2; 前两行
select id,name from test where id=1;
select id,name from test where id=1 and name=‘tom’;
select id,name from test where id=1 or name=‘tom’;
select id,name from test where id>1 and id<4;
select id,name from test where id>2 or id <4;
select id,name from test order by id;
select id,name from test order by id asc; 升序 默认
select id,name from test where id>2 or id <4 order by id desc;

联合查询
select tab.列1 tab2.列n ，tab3.列n form tab1 tab2 tab3 where 表1.列i=表2.列j and 表2.列m=表3.列n order by 表1.列k；
mysql只有表名区分大小写

更新表
update tab1 set 列1=列值 where id=3； 不加条件很严重，灾难

恢复
打开log-bin=mysql-bin
cp /数据库数据目录/mysqlbin.0000098 /back/
#mysqladmin -uroot -p123456 flush-log
后面的操作全写进mysqlbin.000099
恢复全备 #mysql -uroot -p123456 db1 < /back_pro.sql
查看 #mysql -uroot -p123456 -e “select * from db1.test;”
#mysqlbinlog -d db1 mysql-bin.0000098 > /back_98_bin.sql
编辑修改 vi /back_98_bin.sql 更改里面错误的语句 如 去掉没加where的update语句
#mysql -uroot -p123456 db1 < /back_98_bin.sql
然后在吧剩下的binlog恢复进去

查看二进制日志
mysql> show binary logs;

时间点恢复
mysqlbinlog mysql-bin.000018 --start-position=2916 --stop-position=4469 | mysql -uroot -p

防止误删除 -U 没有where或者limit关键字时 不应许update和delete
#mysql = ‘mysql -U’

删除数据
delete from 表 where 不加条件 很危险
truncate table tab 快速清空表内容
delete from test; 逻辑清除，按行删
truncate table test; 更快， 清空物理文件

已有表添加字段
alter table 表名 add 添加列名 类型 其他
alter table tab1 add sex char(4) default not null comment；

添加多字段
alter table tab1 add age int(4) after name ，
add qq varchar(15) first;

改变字段名
alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空；

改字段类型
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

删除字段
ALTER TABLE mytable DROP 字段名;

更改表名
rename table 原表名 to 新表名；
alter table 原表名 rename to 新表名；

删表
drop table tab；

字符集乱码
法一、set names latin1；后再执行select等语句。也可把这条语句写入*.sql文件执行
法二、#mysql -uroot -p123456 --default-character-set=latin1 db1</back.sql
法三、配置文件指定
[mysqld]
default-character-set=latin1 5.1以前的版本
character-set-server=latin1 5.5版本
[client]
default-character-set=latin1

不乱码思想
Linux cat /etc/sysconfig/i18n LANG=“zh_CN.utf8”
服务器 [mysqld]
客户端 临时 set names utf8 永久[client]
库 create database db1 DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci;
表 create table tab1 (
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
程序

查看、更改参数
show variables\G;
show status;
show global status;
更改参数，不重启生效
临时
set global key_buffer_size=
show variables like ‘key_buffer%’;
永久
在配置文件my.cnf 添加 参数值 如key_buffer_size=

字符集长度
SHOW CHARACTER SET;
#mysql -uroot -p123456 -e “SHOW CHARACTER SET;”|egrep “gbk|utf8|latin1” |awk ‘{print $0}’

查看当前数据库字符集
show variables like ‘character_set%’;

客户端的字符集（不需要重启生效）
character_set_client
character_set_connection
character_set_results
注释：相当于
1）“set names 字符集“
2）#mysql -uroot -p123456 --default-character-set=latin1
3) [client]

服务端的字符（需要重启生效）
character_set_database
character_set_server
注释[mysqld]

客户端 服务端 库表

修改数据库、表字符集
alter database character set utf8;
alter table tab1 character set utf8;

已有字符集的库和表修改字符集，先导出数据，修改库、表字符集后再导入数据。

备份
#mysql -uroot -p -B db1 db2 db3 |gzip > /back.sql
#egrep -v “#|*|–|^$” /back.sql
-B 接多个库 增加createdatabase db和use db信息
-A 所有库

不备份，直接显示在屏上
#mysql -uroot -p -B db1

分库备份
#mysql -uroot -p’123456aaa’ -e “show databases;”|grep -Evi “database|infor|perfor” |sed -r ‘s#^([a-z].*$)#mysqldump -uroot -p’123456aaa’ --events -B \1 |gzip >/data/back/\1_bal.sql.gz#g’|bash

法二、
脚本
for dbname in mysql -uroot -p'123456aaa' -e "show databases;"|grep -Evi "database|infor|perfor"
do
mysqldump -uroot -p’123456aaa’ --events -B $dbname|gzip>/data/back/${dbname}_bak.sql.gz
done

备份表
#mysql -uroot -p db1 tab1 tab2 tab3 |gzip > /back.sql
分库分表备份（提示：两个for循环嵌套 show databases; show tables;）

全备
#mysql -uroot -p123456 -A -B --events |gzip > /back.sql
#mysql -uroot -p123456 -A -B -F --events |gzip > /back.sql -F刷新binlog日志
#mysql -uroot -p123456 --master-data=1 db1 |gzip > /back.sql

备份表结构
#mysql -uroot -p123456 db1 tab1 -d > /back.sql

备份数据
#mysql -uroot -p123456 db1 tab1 -t > /back.sql

参数
-A 必须给库 除非指定-B
-B 还原数据时可以不加数据库 source /path/.sql . /path/.sql
-F -flush-logs;
–compact 去掉注释
-d 表结构
-t 表数据
-l --lock-tables lock all tables for read
-x --lock-all-tables
–single-transaction 适合innodb事务数据库备份
–master-data=1 增加binlog日志文件名和对应的位置
–master-data=2 取1和取2的区别，只是后者把 change master … 命令注释起来了，没多大实际区别；–
– Position to start replication or point-in-time recovery from

CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000090’, MASTER_LOG_POS=107;

–

help mysqldump |less

for myisam
#mysql -uroot -p123456 -A -B -F -x --master-data=1 --flush-privileges --triggers --routines --events |gzip > /back.sql

for innodb
#mysql -uroot -p123456 -A -B -F --single-transaction --master-data=1 --flush-privileges --triggers --routines --events |gzip > /back.sql
注释 --triggers 触发器
–routines 存储过程

在mysql里面查看系统文件 system
mysql>system ls /data

gzip -d *.gz -d 解压后会删除原文件

分库分表恢复
#for dbname in `ls *.sql|sed ‘s#_bak.sql##g’;do mysql -uroot -p1233456 < ${dbname}_bak.sql;done

#mysql -uroot -p123456 -e “set names latin1;use db1;select * from tab1;”`
#mysql -uroot -p123456 -e “show full processlist;”|grep -v “select …”

查看数据库状态信息
show status; 当前会话的数据库状态信息
shwo global status; 整个数据库运行状态信息

查看正在执行的完整的sql语句
show full processlist

查看数据库参数信息
show variables;

修改数据库参数
set global key_buffer_size=1024102432 直接生效 重启后失效，永久生效在my.cnf里修改参数重启数据库。
values 为ON|OFF 的需要在配置文件里面更改，set更改不了。

截取指定库的binlog
#mysqlbinlog -d db1 mysql-bin.000144 r recovery.sql
位置恢复
#mysqlbinlog --start-position=197 --stop-position=301 /binlog/mysql-bin.000014 r >recovery.sql

时间恢复
#mysqlbinlog --start-datetime=‘2018-04-18 17:27:29’ --stop-datetime=‘2018-04-18 17:32:01’ mysql_bin.000001 > recovery.sql

查看MySQL Server上的二进制日志
mysql> show binary logs;
±--------------------±----------+
| Log_name | File_size |
±--------------------±----------+
| VMS00781-bin.000001 | 372 |
| VMS00781-bin.000002 | 515 |
±--------------------±----------+
查看二进制日志中的事件
mysql> show binlog events;
±--------------------±----±------------±----------±------------±--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
±--------------------±----±------------±----------±------------±--------------------------------------+
| VMS00781-bin.000001 | 4 | Format_desc | 36 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 |
| VMS00781-bin.000001 | 120 | Query | 36 | 192 | BEGIN |
| VMS00781-bin.000001 | 192 | Table_map | 36 | 238 | table_id: 204 (test.ab) |
| VMS00781-bin.000001 | 238 | Delete_rows | 36 | 291 | table_id: 204 flags: STMT_END_F |
| VMS00781-bin.000001 | 291 | Xid | 36 | 322 | COMMIT /* xid=289981 */ |
| VMS00781-bin.000001 | 322 | Rotate | 36 | 372 | VMS00781-bin.000002;pos=4 |
±--------------------±----±------------±----------±------------±--------------------------------------+

mysqlbinlog常用参数

–disable-log-bin或-D 在解析的语句中添加 set sql_log_bin=0 可以禁止恢复过程产生日志

-r 或–result-file 结果文件保存路径

-server-id 指定mysql服务器，确保是由给定服务器id的mysql服务器所生成的日志

–database=db_name，-d db_name只列出该数据库的条目

–offset=N，-o N跳过前N个条目

–start-position=N

mysql 主从复制
mysql主 开启binlog日志 io线程 server-id 授权从库连接主库的用户
mysql从 中继日志relay io线程 sql线程 server-id 从小于主

master info（上一次同步的日志位置）
从库拉去的是binlog日志内容
从库执行完sql语句后会删除日志内容

多实例启动 加配置文件 登录加sock
案例：主库3306 从库3307
主库开启binlog日志
grep ‘’
授权从库连接主库的用户
mysql>grant replication slave on . to ‘rep’@‘从库ip’ identified by ‘123456’;
mysql>GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT,PROCESS ON . TO bakmysqltest@‘localhost’;
FLUSH PRIVILEGES;
mysql>flush privileges;
mysql>flush table with read lock;
mysql>show master status;
窗口不能断，重新克隆个窗口
#mysqldump -uroot -p1233456 -S /data/3306/mysql.sock -A -B --master-data=2 --events>/opt/rep.sql #新窗口下执行

mysql>unlock tables；#原来的窗口下执行

从库上执行
#mysqldump -uroot -p123456 -S /data/3307/mysql.sock </opt/rep.sql
mysql>CHANGE MASTER TO
MASTER_HOST=‘主库ip’,
MASTER_PORT=‘3306’,
MASTER_USER=‘rep’,
MASTER_PASSWORD=‘1213456’, 如果备份的时候加的参数是–master-data=1 后面两个参数可以不写
MASTER_LOG_FILE=‘mysql-bin.000003’,
MASTER_LOG_POS=333;
注意单引号中不能用空格，外面用逗号
mysql>start slave;
mysql>show slave status\G 查看Slave_IO_Running:Yes 和Slave_SQL_Running:Yes 和 Seconds_Behind_Master:0 落后主库的秒数

查看bin-log 和server-id
#egrep “bin-log|server-id” /data/3306/my.cnf
上面两个参数必须放在[mysqld]下

多实例登录必须指定sock 查看mysql参数配置
mysql -uroot -p123456 -S /data/3306/mysql.sock -e “show variables like ‘log_bin’;”

mysql>show master logs;

故障
show master status没结果，可能原因
主库binlog没开 #egrep “bin-log|server-id” /data/3306/my.cnf
mysql>show variables like ‘server_id’;
mysql>show variables like ‘log_bin’;

故障
CHANGE MASTER 时多了空格错误
错误提示 Last_IO_Error:Got fatal error 1236 from master when reading data from binary log :‘ciykd bit fubd furst kig fuke bane ub bubart kig ubdex fuke’

使用–maser-data=1 进行备份文件会增加如下内容：
CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000003’,MASTER_LOG_POS=333;
更适合主从复制

对主数据库锁表只读（当前窗口不要关掉，关闭失效，超时失效）
mysql>flush tables with read lock; 5.1版本
mysql>flush table with read lock; 5.5版本
会受下面参数的控制，如果超时不操作会自动解锁
interactive_timeout =28800
wait_timeout =28800
默认情况下的时长为
mysql>show variables like ‘%timeout%’;

从库关键文件 master.info relay-log relay-info
如果从库还想级联从库，需要打开log-bin和log-slave-updates参数

cat mysql_bak.sh
#! /bin/sh
MYUSER=root
MYPASS=“bak”
MYSOCK=/data/3306/mysql.sock
MAIN_PATH=/server/backup
DATA_PATH=/server/backup
LOG_FILE=$DAT{A}_{P}ATH/mysq{l}_{b}acku{p}_{‘}date+FDAT{A}_{F}ILE=$DATA_PATH{}

mysqldump -e参数

–extended-insert, -e
使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用–skip-extended-insert取消选项。

mysql>show processlist; 查看主从库三个线程的状态

授权方案：
web db1 主库ip：3306（select，insert,delete,update）
web db1 从库ip：3306（select）

不同步mysql库，主从库分别进去如下授权
主：web db1 主库ip：3306（select，insert,delete,update）
从：web db1 从库ip：3306（select）
缺点：主从切换需要执行权限问题

binlog-ignore-db = mysql
binlog-ignore-db = preformance_schema
binlog-ignore-db = information_schema

5.1 5.5版本
read-only参数 也可启动的时候 --read-only
从库配置 只允许普通用户读
my.cn里面加入read-only后，重启生效。
root用户和all权限的用户可以插入、删除数据
具备insert,update,delete等权限的用户无法通过read-only规则

master端
–binlog-do-db 二进制日志记录的数据库，多个库有逗号隔开
–binlog-ignore-db 二进制日志忽略的数据库 多个库用逗号隔开
–replication-do-db 需要复制的数据库
–replication-ignore-db 忽略需要复制的数据库
–replication-do-table 需要复制的表
–replication-ignore-table 忽略需要复制的表
–replication-wild-do-table 同–replication-do-table 可以加通配符
–replication-wild-ignore-table 同 --replication-ignore-table 可以加通配符

故障1
show slave status;报错 slave_IO_Running:Yes
slave_SQL_Running:NO
Last_SQL_Error:1007 mysql错误代码 1007：数据库已存在，创建数据库失败

解决方法：
stop slave;
set global sql_slave_skip_counter =1;
start slave;

从库可以跳过的代码 --slave-skip-errors =1032,1062,1007
–slave-skip-errors =1053,1062
–slave-skip-errors =all

skip-name-resolve 忽略名字解析

级联 在从库上开启如下功能
开启log-bin=mysql-bin
log-slave-updates

删除binlog日志
expire_logs_days = 7
也可命令删除：find /data/3306/ -type f -name “mysql-bin.000*” -mtime +7|xargs rm -f

主从切换
从库上执行 mysql>show processlist\G 查看状态 state
登录所有从库 分别查看master.info 查看谁的mysql-bin.00n 以及位置谁更大
确保relay log 全部更新完毕
stop slave io_thread；
show processlist 看到Has read all relay log; 表示从库更新都执行完毕
2、登录
从库切为主库
mysql -uroot -p123456 -S /data/3307/mysql.sock
stop slave;
retset master;
quit ，/data/3307/mysql restart
3、cd /data/3307/data rm -f master.info relay-log.info 删除master.info relay-log.info
4、开启binlog 注释log-slave-updates read-only等参数 检查授权表
5、登录从库
stop slave;
change master to master_host = ‘主库ip’ …
start slave;
show slave status \G

新的主库还需要拉取原主库的logbin日志进行恢复

主主解决主键自增长变量冲突
master1
auto_increment_increment =2 自增ID间隔 1 3 5 7
auto_increment_offset =1 ID初始位置
log-slave-updates
log-bin=

master2
auto_increment_increment =2 自增ID间隔 2 4 6 8
auto_increment_offset =2 ID初始位置
log-slave-updates
log-bin=

skip-slave-start 启动时忽略从库启动 用于互为主从的参数

不用主从同步 rsync拉取binlog备份到远程服务器
rsync-avz /data/3306/mysql-bin.00* rsync_backup@10.1.1.1::backup --password-file=/etc/rsync.password