【MYSQL】常用命令備忘錄

標籤：code   pre   erro   pos   sse   ict   cond   use   pass   

• source

　　　　不接路徑時，尋找檔案的路徑是執行mysql命令時的路徑

　　　　接路徑時，使用路徑+filename

[[email protected] mysql]# vi demo.mysql [[email protected] mysql]# pwd/root/mysql[root@wordpressserver mysql]# ls -altotal 12drwxr-xr-x.  2 root root 4096 Nov  2 21:45 .dr-xr-x---. 31 root root 4096 Nov  2 21:49 ..-rw-r--r--.  1 root root   87 Nov  2 21:45 demo.mysql[root@wordpressserver mysql]# mysql> source demo.mysqlERROR: Failed to open file ‘demo.mysql‘, error: 2mysql> source /root/mysql/demo.mysqlDatabase changedQuery OK, 0 rows affected (0.01 sec)mysql> show tables;+----------------+| Tables_in_demo |+----------------+| example        |+----------------+1 row in set (0.00 sec)

• 　　mysqldump 匯出一個表，使用sql檔案恢複表

[[email protected] ~]# mysqldump -u root -p demodabasename exampletablename > sqldumptest1.sqlEnter password: [root@wordpressserver ~]# lsanaconda-ks.cfg  Desktop    Downloads  mysql     post-install      Public            Templates  vmtool             wordpress      wordpress-4.4.2-zh_CN.tar.gzcore.3990        Documents  Music      Pictures  post-install.log  sqldumptest1.sql  Videos     vmware-install.pl  wordpress (2)[root@wordpressserver ~]# vi sqldumptest1.sql [[email protected] ~]# mysql -uroot -f demo < sqldumptest1.sqlERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: NO)[root@wordpressserver ~]# mysql -uroot -p123456 -f demo < sqldumptest1.sql[[email protected] ~]# 

或者

[[email protected] ~]# mysql -uroot -p123456 -f demo < /root/mysql/demo.mysql
-f是忽略錯誤然後繼續執行
mysql> show tables;+----------------+| Tables_in_demo |+----------------+| example        |+----------------+1 row in set (0.00 sec)mysql> desc example;+-------+--------------+------+-----+---------+-------+| Field | Type         | Null | Key | Default | Extra |+-------+--------------+------+-----+---------+-------+| id    | int(11)      | YES  |     | NULL    |       || data  | varchar(100) | YES  |     | NULL    |       |+-------+--------------+------+-----+---------+-------+2 rows in set (0.00 sec)

 

shell> mysqladmin create target_db_name
shell> mysql target_db_name < backup-file.sql
就是
shell> mysql 庫名 < 檔案名稱

或者

B:使用mysqldump命令

mysqldump -u username -p dbname < filename.sql

C:使用mysql命令

mysql -u username -p -D dbname < filename.sql

【MYSQL】常用命令備忘錄