1. 登陸MYSQL
mysql -u${user_name}
mysql -u${user_name} -p${password}
-u -p參數(shù)后沒(méi)有空格
2. 創(chuàng)建數(shù)據(jù)庫(kù)
CREATE DATABASE `${database_name}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci；
例：CREATE DATABASE `test_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci；
3. 創(chuàng)建MYSQL用戶
創(chuàng)建一個(gè)沒(méi)有任何權(quán)限的賬號(hào)
CREATE USER '${username}'@'${host}' IDENTIFIED BY '${password}'；
例：CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
授權(quán)給創(chuàng)建的賬號(hào)
GRANT ${permission} ON ${database name}.${table name} TO '${username}'@'${host}';
例：GRANT ALL PRIVILEGES ON *.* TO 'newuser'@'localhost';
授權(quán)只讀權(quán)限
GRANT SELECT ON *.* TO 'pasa'@'localhost';
權(quán)限生效
FLUSH PRIVILEGES;
刪除用戶
DROP USER '${user}'@'${host}';
例如：drop user 'root'@'::1';
drop user 'root'@'iz28uzbk2yaz';
drop user 'root'@'127.0.0.1';
drop user ''@'localhost';
drop user ''@'iz28uzbk2yaz';
4、設(shè)定mysql root賬戶密碼
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('${password}');
5、創(chuàng)建數(shù)據(jù)庫(kù)表
CREATE TABLE IF NOT EXISTS tasks (
task_id INT(11) NOT NULL AUTO_INCREMENT,
subject VARCHAR(45) DEFAULT NULL,
start_date DATE DEFAULT NULL,
end_date DATE DEFAULT NULL,
description VARCHAR(200) DEFAULT NULL,
PRIMARY KEY (task_id) ) ENGINE=MyISAM;
6、修改MYSQL數(shù)據(jù)存儲(chǔ)路徑
a、systemctl stop mysql
systemctl status mysql
b、mv /var/lib/mysql? /path
c、vim /etc/my.cnf，修改如下配置：
datadir=/var/lib/mysql? ----> datadir=/path/mysql;
socket=/path/mysql/mysql.sock ----> socket=/var/lib/mysql/mysql.sock;
log-error=/var/log/mysqld.log ----> log-error=/path/logs/mysql/mysqld.log
d、ln -s /path/mysql /var/lib/mysql，如果不調(diào)用mysql命令本地訪問(wèn)mysql，可以不用設(shè)置。
e、systemctl start mysql
注意：被mysql server使用的文件需要有mysql用戶，mysql組的權(quán)限.
7、導(dǎo)入導(dǎo)出SQL腳本
導(dǎo)入mysql腳本
登入mysql：? mysql> source /${path}/test.sql;
導(dǎo)出mysql腳本
mysqldump -u${user} -p ${database} > mysql_backup.sql
mysqldump -u${user}-p --database mysql test > my_databases.sql
mysqldump -u${user}-p --all-databases > all_databases.sql
導(dǎo)出數(shù)據(jù)庫(kù)結(jié)構(gòu)
mysqldump -u${user} -p -d --add-drop-table mysql > mysql_define.sql
例：mysqldump -uroot -p baidu_user > baidu_user.sql
8、撤銷用戶權(quán)限
REVOKE ALL PRIVILEGES ON *.* FROM 'pig'@'%'；
9、刪除數(shù)據(jù)庫(kù)和數(shù)據(jù)表
drop database ${dbname};
drop? table ${table};
例：drop database baidu_user;
此處僅是一些常用的指令用法，歡迎各位讀者學(xué)習(xí)交流。
