生产中mysql常用命令

1. 添加root用户并赋予权限

	use mysql;
	create user 'root'@'%' identified by '123456';
	grant all on *.* to root@'%' identified by '123456';

2. mysql查看当前所有数据库大小

select table_schema, concat(truncate(sum(data_length)/1024/1024,2),' mb') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'mb') as index_size from information_schema.tables group by table_schema order by data_size desc;

获取数据库中所有库总和大小
select concat(round(sum(DATA_LENGTH/1024/1024), 2), 'MB') as data_size from tables;

3. 查看文件

a. 查看my.ini或my.conf路径

select @@basedir;
 /rdsdbdata/db/ 

b. 查看文件存储路径

select @@datadir;

4. mysqldump使用案例

备份，错误的直接忽略， Set slave_exec_mode=IDEMPOTENT #It will suppress duplicate-key and no-key-found errors. Default slave_exec_mode is STRICT means no such suppression takes place.

mysqldump --all-databases -u root -p'aaaaaaaaaaaaaaadanmie' --ignore-table=bottle_l.t_game_copy1 -B |gzip >/data/backup/mysql_bak.$(date +%F).sql.gz