# 本機
mysql -u root -p
# 遠程
mysql -h 110.110.110.110 -u root -p'abcd123'
# 退出
exit
# 首次設置
mysqladmin -u root password 'ab12'
# 修改
mysqladmin -u root -p'ab12' password 'djg345'
-- 遠程全權限超管(極高危,僅演示)
GRANT ALL PRIVILEGES ON *.* TO 'test1'@'%' IDENTIFIED BY 'abc';
-- 本地業務賬號(推薦)
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'test2'@'localhost' IDENTIFIED BY 'abc';
-- 移除密碼
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'test2'@'localhost' IDENTIFIED BY '';
SHOW DATABASES;
USE mysql;
SHOW TABLES;
DESCRIBE user;
CREATE DATABASE school;
USE school;
CREATE TABLE teacher (
id INT(3) AUTO_INCREMENT PRIMARY KEY,
name CHAR(10) NOT NULL,
address VARCHAR(50) DEFAULT '深圳',
year DATE
);
INSERT INTO teacher VALUES ('', 'glchengang', '深圳一中', '1976-10-10');
INSERT INTO teacher VALUES ('', 'jack', '深圳一中', '1975-12-23');
SELECT * FROM teacher;
DELETE FROM teacher;
DROP TABLE teacher;
DROP DATABASE school;
# 將建庫建表插入語句保存為 school.sql
mysql -u root -p < school.sql
文件格式:字段間 Tab 分隔,NULL 用 \N 表示。
3 rose 深圳二中 1976-10-10
4 mike 深圳一中 1975-12-23
USE school;
LOAD DATA LOCAL INFILE 'teacher.txt' INTO TABLE teacher;
# 完整備份
mysqldump --opt school > school.bak
# 指定用戶密碼備份
mysqldump -u root -p'21century' --databases dbname > mysql.dbname
# 恢復
mysqladmin -u root -p create dbname
mysql -u root -p dbname < mysql.dbname
# 或
mysqlimport -u root -p'21century' < mysql.dbname
RENAME TABLE ztemp TO ztemp4;
mysqladmin -u root password 'newpass'
# 啟動 (3.23.15+ 需指定用戶)
/usr/local/bin/safe_mysqld --user=mysql &
# 停止
mysqladmin -u root -p shutdown