MySQL常用命令和配置

忽略一些表导出数据库

mysqldump -h 10.66.125.xx -u xx -p xx -t test_db --ignore-table=test_db.manalog --ignore-table=test_db.user_play_history  > test_db_bak.sql; 

 

全部导出

mysqldump -u xxx -p xxx > test_db.sql

 

去除权限

sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/ ' backup.sql > new_backup.sql

 

去除注释

sed -i '/^\//d' test.sql

 

 

创建用户

CREATE USER 'julian'@'localhost' IDENTIFIED BY '123456';

 

权限

grant all privileges on *.* to  'julian'@'%' identified by '123456';

flush privileges;

 

 

创建发生错误，需要修改my.cnf

insert into mysql.user(Host,User,Password)  values("localhost","julian",password("123456")); 

 

 

忽略表名大小写

lower_case_table_names=1

default-character-set=utf8

 

max_allowed_packet字节的信息包设置

show global variables like 'max_allowed_packet';

set global max_allowed_packet = 200*1024*1024;  //200M

修改/etc/my.cnf

max_allowed_packet=16M

　　

命令行创建数据库：

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

create database db_name default character set utf8mb4 collate utf8mb4_unicode_ci; //uft-8

 

追加字段：

ALTER TABLE album ADD COLUMN flag varchar(2) DEFAULT '0' COMMENT 'flag' AFTER unicast;

 

查看表：

show create table queue_detail_selections_show\G

 

导入sql文件：

mysql>source /home/dbname.sql

 查询数据库表的大小：

SELECT TABLE_NAME,DATA_LENGTH,INDEX_LENGTH,(DATA_LENGTH+INDEX_LENGTH) as length,TABLE_ROWS,concat(round((DATA_LENGTH+INDEX_LENGTH)/1024/1024,3), 'MB') as total_size FROM information_schema.TABLES WHERE TABLE_SCHEMA='databases' order by length desc