mysql操作命令

1.客户端登录
mysql -u root -p sercet -h 127.0.0.1 -P 3306
2.更改用户密码
set password=password("123456’’);
3.取消只读
show global variables like ‘%read_only%’;
set global read_only=off;
4.创建用户
#%指限制可用该用户登录的ip
create user ‘username’@’%’ IDENTIFIED BY ‘password’
5.创建数据库
create database dbname character set utf8 collate utf_general_ci;
6.赋予用户数据库操作权限
#all表示所有权限，也可以是select、delete、update、insert
#dbname.* 表示库下的所有表
grant all on dbname.* to username@’%’;
7.删除用户操作权限
revoke all on dbname.* from username@’%’;
8.刷新数据库权限
FLUSH PRIVILEGES;

9.导入数据

source /tmp/mysql.sql
mysql -uroot -psercet dbname < /tmp/mysql.sql

10.导出数据

1)导出整个库
mysqldump -uroot -psercet --single-transaction -R dbname > /tmp/mysql.sql
2)导出某个表
mysqldump -uroot -psercet --single-transaction dbname table > /tmp/mysql.sql
3)导出表中的某部分数据
mysqldump -uroot -psercet --single-transaction dbname table --where=“id=1 and name=’'xxx” > /tmp/mysql.sql
4)只导出表结构
mysqldump -uroot -psercet --single-transaction -d dbname table > /tmp/mysql.sql

11.不交互执行命令

#\G格式化结果
mysql -uroot -psercet -e “select * from table \G” dbname;

12.查询数据库中表的个数

select count(*) tables,table_schema from information_schema.tables where table_schema = ‘dbname’ group by table_schema;

13.mysql主从复制方式

1)主库
create user ‘rsyncdb’@’%’ identified by ‘sercet’;
grant replication slave on . to ‘rsyncdb’@’%’;
flush privileges;
2)备库
change master to
master_host=‘master_ip’,
master_port=3306,
master_user=‘rsyncdb’,
master_password=‘sercet’,
master_auto_position=1;
start slave;
3)查看slave状态
show slave status \G;
4)停止slave
stop slave;
5)清除slave信息
reset slave all;
6)主从复制错误解决方案
问题：先对备库数据操作，再次对主库进行操作导致主从复制异常
解决方案：修改主从复制模式，默认主从复制模式是strict(严格的)，可进行修改为idempotent(幂等)来解决
操作步骤：stop slave;set global slave_exec_mode=“idempotent”;start slave;set global slave_exec_mode=“strict”;

14.查询数据库中表的数据大小

select table_name, CONCAT(round((table_size/1024/1024),2),‘MB’) from
(select table_name, data_length+index_length table_size
from information_schema.tables where information_schema.tables.table_schema=‘database_name’) t1
order by table_size desc limit 10;

15.查询表最后更新时间

SELECT
TABLE_NAME, UPDATE_TIME
FROM
information_schema.TABLES
WHERE
information_schema.TABLES.TABLE_SCHEMA = ‘DB_NAME’
AND
information_schema.TABLES.TABLE_NAME = ‘TABLE_NAME’;

16.获取sql执行时间

  set profiling=1;
  show profiles;

17.binlog日志

开启binlog日志(在[mysqld]下修改或添加如下配置)：

server-id=1
log-bin=mysql-bin
binlog_format=MIXED

binlog日志模式

 Mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，
 基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)
 。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。
 
1、STATEMENT模式（SBR）
每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行
的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave
中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

2、ROW模式（RBR）
不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某
些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会
产生大量的日志，尤其是alter table的时候会让日志暴涨。

3、MIXED模式（MBR）
以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式
无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

查看默认的日志保存天数

show variables like '%expire_logs_days%';
set global expire_logs_days=off;
0-表示永不过期

设置为7天有效期(修改配置文件)

expire_logs_days=7

binlog日志导出

mysqlbinlog  --base64-output=decode-rows -v --start-date='2014-09-16 14:00:00' --stop-date='2014-09-16 14:20:00'  mysql-bin.000017 >log.txt