mysql

CRUD

insert

1	INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name

alter

修改编码

1 2	ALTER TABLE `article` CHANGE COLUMN `breif` `breif` TEXT CHARACTER SET 'utf8mb4' NULL DEFAULT NULL ;

conmand

mysqldump

导出整个数据库

1	mysqldump -u [uname] -p[pass] db_name > db_backup.sql

导出所有的数据库

1	mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql

指定数据库中的表

1	mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql

gzip 压缩

1	mysqldump -u [uname] -p[pass] db_name \| gzip > db_backup.sql.gz

其他

1 2	mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql mysqldump --single-transaction -u corly-p -v --host=mysql.corly.cc shu_student > shu_student.sql

import

1	mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

import large csv file

修改MySQL的配置文件，my.cnf
[mysqld]
max_allowed_packet=1000M

执行如下命令导入csv文件

mysql -uroot -proot --local-infile shu_student -e "
    load data local infile '~/mengshuang/canteen/output2/part.csv'                    
        into table canteen_result
    fields terminated by ','
    lines terminated by '\n'"

###自动备份数据库

# /bin/bash
DB_NAME=""
DB_USER=""
DB_PASS=""
BIN_DIR="/usr/bin"
BACK_DIR="/root/backdata"
DATE="mysql-`date +'%Y%m%d-%H:%M:%S'`"
LogFile="$BACK_DIR"/dbback.log
BackNewFile=$DATE.sql
$BIN_DIR/mysqldump --opt --force -u$DB_USER -p$DB_PASS $DB_NAME > $BACK_DIR/$DATE.sql
echo -----------------------"$(date +"%y-%m-%d %H:%M:%S")"----------------------- >> $LogFile
echo  createFile:"$BackNewFile" >> $LogFile

# 删除7天之前的备份
find "$BACK_DIR" -ctime +7 -type f -name "*.sql" -print > "$BACK_DIR"/deleted.log
echo -e "delete files:" >> $LogFile
cat $BACK_DIR/deleted.log | while read LINE
do
	rm -rf $LINE
	echo $LINE>> $LogFile
done
echo "---------------------------------------------------------------" >> $LogFile

用户管理

查看所有用户

1	SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

创建用户

1	CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符**%**
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

示例

1
2
3

CREATE USER 'web'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'web'@'192.168.1.101' IDENDIFIED BY '123456';
CREATE USER 'web'@'%' IDENTIFIED BY '123456';

授权

1	GRANT privileges ON databasename.tablename TO 'username'@'host';

privileges：用户的操作权限，如select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*

示例

1 2	GRANT SELECT, INSERT ON test.user TO 'web'@'%'; GRANT ALL ON test.* TO 'web'@'%';

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
1
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

更改密码

1	SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用

1	SET PASSWORD = PASSWORD("newpassword");

删除用户

1	DROP USER 'username'@'host';

其他

查看数据库占用空间大小

SELECT table_schema                                        "DB Name", 
   Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM   information_schema.tables 
GROUP  BY table_schema;