MySQL export and import data command

MySQL Export Import Data command 1. export mysqldump-u user name-p Database Name> exported file name mysqldump-u wcnc-p smgp_1__wcnc> wcnc. SQL 2. export a table mysqldump-u user name-p database name Table Name> exported file name mysqldump-u wcnc-p smgp_apps_wcnc users> wcnc_users. SQL 3. export a database structure mysqldump-u wcnc-p-d -- add-drop-table smgp_1__wcnc> d: \ wcnc_db. SQL-d has no data -- add-drop-table adds a drop table before each create statement
4. import the common source commands of the database www.2cto.com To Go To The mysql Database Console, for example, mysql-u root-p mysql> use the database and then use the source command. The following parameter is the script file (as used here. SQL) mysql> source d: \ wcnc_db. SQL www.2cto.com 1. Example 1: MYSQL connected to the local machine first opens the DOS window and then enters the bin directory under the mysql installation directory, for example: d: \ mysql \ bin, enter the mysql-uroot-p command, and press enter to prompt you to enter the password. If you have just installed MYSQL, the Super User root has no password, so press enter to enter MYSQL. The MYSQL prompt is: mysql> 2. Example 2: connect to MYSQL on the remote host. Assume that the IP address of the remote host is 10.0.0.1, the username is root and the password is 123. Run the following command: mysql-h10.0.0.1-uroot-p123 (Note: you do not need to add spaces for u and root, and the rest are the same). 3. Run the MYSQL Command exit (Press ENTER) www.2cto.com (2) change password: Format: mysqladmin-u username-p old password new password 1. Example 1: Add a password 123 to the root user. First, enter the directory C: \ mysql \ bin in DOS, and then type the following command: mysqladmin-uroot-password 123 Note: because the root has no password at the beginning, therefore, the old-p password can be omitted. 2. Example 2: Change the root password to 456 www.2cto.com mysqladmin-uroot-pab12 password 456 (3) Add a new user: (Note: different from the above, the following is a command in the MYSQL environment, so a semicolon is followed as the command Terminator.) Format: grant select on database. * to username @ login host identified by "password" Example 1. Add a user test1 whose password is abc so that he can log on to any host, all databases are permitted to query, insert, modify, and delete databases. First, use the root user to connect to MYSQL, and then type the following command: grant select, insert, update, delete on *. * to test1 @ "%" Identified by "abc"; however, the added user in Example 1 is very dangerous. If someone knows the password of test1, then he can log on to your mysql database on any computer on the internet and do whatever he wants for your data. For the solution, see Example 2. Example 2: Add a user named "test2" with the password "abc" so that the user can only log on to localhost, you can also query, insert, modify, and delete the database mydb (localhost refers to the local host, that is, the host where the MYSQL database is located), so that the user knows the password of test2, he cannot access the database directly from the internet, but can only access the database through the web pages on the MYSQL host. Grant select, insert, update, delete on mydb. * to test2 @ localhost identified by "abc"; if you do not want test2 to have a password, you can run another command to cancel the password. Grant select, insert, update, delete on mydb. * to test2 @ localhost identified "";
(4) display command 1. Display database list: show databases; two databases: mysql and test at the beginning. The mysql database contains the MYSQL system information. We change the password and add new users to use this database for operations. 2. display the data table in the database: www.2cto.com use mysql; // open the database show tables; 3. display the data table structure: describe table name; 4. create a database: www.2cto.com create database name; 5. create a table: use database Name; create table Name (field setting list); 6. delete database and table: drop database name; drop table name; 7. Clear the records in the Table: delete from table name; 8. display the records in the Table: select * from table name; from IT expert network