#数据库主从
show processlist;
show master statusG;
show variables like 'server_id';
flush privileges;
select user,host,password from mysql.user;
#主上执行
grant replication slave,file on *.* to 'slavebak'@'1.1.1.1' identified by 'newpasswd';
#在从上执行以下
change master to master_host='1.1.1.1', master_user='test', master_password='newpasswd', master_log_file='mysql-bin.000005', master_log_pos=340;
change master to master_host='1.1.1.1', master_user='slavebak', master_password='newpasswd',MASTER_AUTO_POSITION=1;
#权限赋予
flush tables with read lock;
unlock tables;
CREATE USER auditor@localhost IDENTIFIED BY 'newpasswd';
GRANT ALL ON yiibaidb.* TO auditor@localhost;
grant create,execute,insert,delete,select on *.* to usertest1@localhost;
grant create,execute,insert,delete,select on *.* to usertest1@'%' identified by 'newpasswd';
SHOW GRANTS FOR usertest1@'%';
#字符集编码修改
create database if not exists test3 character set utf8;
alter database test3 character set latin1;
#修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码';
update user set password=password("12345") where user="root";
忘记root密码，停数据库 使用
mysqld_safe --skip-grant-tables
set password = password("newpasswd");
#允许远程登录
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1234' WITH GRANT OPTION;
flush privileges;
#查看同步状态参数
Seconds_Behind_Master，为0说明同步完成
pt-table-checksum 检查数据一致性，会有影响（注意）。
#查看数据库表的行数
use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'mysql' order by table_rows desc;
查看单表行数
select table_name,table_rows from information_schema.tables where TABLE_NAME = 'history' order by table_rows;
select table_name,table_rows from tables where TABLE_NAME = 'orders' order by table_rows ;
select table_name,table_rows from tables where TABLE_NAME = 'books' order by table_rows ;
select table_name,table_rows from tables where TABLE_NAME = 'book_parts' order by table_rows ;
#查询数据库中的存储过程和函数
select `name` from mysql.proc where db = 'xx' and `type` = 'PROCEDURE' ; #存储过程
select `name` from mysql.proc where db = 'xx' and `type` = 'FUNCTION' ; #函数
show procedure status; #存储过程
show function status; #函数
查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;
查看视图
SELECT * from information_schema.VIEWS #视图
SELECT * from information_schema.TABLES #表
查看触发器
SHOW TRIGGERS [FROM db_name] [LIKE expr]
SELECT * FROM triggers T WHERE trigger_name=”mytrigger” G
#查看数据库大小
1、进入information_schema 数据库（存放了其他的数据库的信息）
use information_schema;
2、查询所有数据的大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
3、查看指定数据库的大小：
比如查看数据库home的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='home';
4、查看指定数据库的某个表的大小
比如查看数据库home中 members 表的大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='home' and table_name='members';
5，看每个表大小
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') as data FROM TABLES WHERE TABLE_SCHEMA='mysql';
设置从机的复制模式
change master to master_auto_position=0;
reset slave all ;
查看是否安全保护
SHOW VARIABLES LIKE 'sql_safe_updates';
select @@sql_safe_updates;
检查数据一致性
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --create-replicate-table --databases=ifeng --tables= -u root -p eba41b786d57213b09b12ca38ed9fa63 -h 127.0.0.1
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format
--replicate=ifeng --create-replicate-table --databases=ifeng --tables=book_parts -u chechdb -p 123comER -h 10.89.11.152
grant all on *.* to chechdb@'10.89.%.%' identified by '123comER';
死锁
show processlist;
1：查看当前的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
2：查看当前锁定的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
3：查看当前等锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
sql
后10条数据
select id,createTime from message_filesn order by id desc limit 10;
select id,createTime from message_filesn where createTime < DATE_SUB(CURDATE(),INTERVAL 1 day) order by id asc limit 10;
SELECT DATE_SUB(CURDATE(),INTERVAL 10 day) 2018-08-19
SELECT DATE_SUB(CURDATE(),INTERVAL 1 year) 2017-08-29
SELECT DATE_SUB(CURDATE(),INTERVAL 3 month) 2018-05-29
SELECT DATE_SUB(CURDATE(),INTERVAL -1 month) 2018-09-29
释放表空间
optimize table tables_name;
改字符集
SET character_set_client = utf8;
SET character_set_results = utf8;
SET character_set_connection = utf8;
SET character_set_database = utf8;
SET character_set_server = utf8;
SET character_set_system = utf8;
改表名
alter table message_filesntest rename to message_filesn ;