Mysql remote machine imports and exports data, locks the table or not locks part of the data or all of the table

黄舟

Release： 2017-03-02 16:39:38

Original

2922 people have browsed it

When using mysql recently, I often encounter the situation of exporting or importing data. For some simple ones, you can directly use navicate to export the results directly. However, many times, because the machine is on the remote side and is an online machine. In this case, when mysql cannot be linked to mysql after a jump, navigate cannot use this method to export. I searched some articles on the Internet and summarized the import and export of mysql, as follows:

1. Export the database

mysqldump -h 127.0.0.1  -u name -p  dbname >name.sql

Copy after login

Export the database dbname of 127.0.0.1 to the name.sql file

2. Export a certain table of the database

mysqldump -h 127.0.0.1  -u username -ppassword  dbname tablename >name.sql

Copy after login

Export the tablename table in the database dbname on ip 127.0.0.1 to the name.sql file

3. Export data from a table in the database that meets certain conditions

mysqldump -h 127.0.0.1  -u username -ppassword  dbname tablename  --where="id>10 limit 10000" >name.sql

Copy after login

The situations one and two above are both All tables or libraries are exported, but sometimes we only want part of the data. This part of the data meets certain conditions. For the above structure,

put the tablename table in the database dbname Extract 100 pieces of data with ID>10 and export them to the name.sql file

4. Export a certain table of the database without locking the table

mysqldump -h 127.0.0.1  -u username -ppassword --single-transaction dbname  tablename>name.sql

Copy after login

Sometimes you don’t have the permission to lock the table when exporting. For example, if you only have read-only permission, you probably don’t have the permission to lock the table. In this case, just add --single-transaction. Export without locking the table

The above will lock the database table for the first, second and third cases. Adding this command can achieve the purpose of exporting data without locking the table

5. Export the table structure of a certain table in the database without exporting the data

mysqldump -h 127.0.0.1  -u username -ppassword --single-transaction -d dbname  tablename>name.sql

Copy after login

Sometimes we don’t want to export the data, but just export the table Structure, for example, you want to completely copy and create the table structure according to the table structure. In this case, there are many methods that can be used. For example, you can directly connect to the database and use the show create table tablename command to get the table creation statement; This is just another way to export the table structure. If you want to export only the table structure, you can add -d.

6. Export the data of a certain table in the database without exporting the structure

mysqldump -h 127.0.0.1  -u username -ppassword --single-transaction -t dbname  tablename>name.sql

Copy after login

If you want to export only the data but not the table structure, you can use -t to achieve the purpose. The original purpose of using this sentence is In order to export the view in the database, because I don’t have the permission to show create view, I can only try to export the data only. It turns out that it is not possible. I will think about it again and mark it again so that it can be used later

7. Database to import

use dbname;
set names gbk;
source /d1/name/name.sql;

Copy after login

When importing data, just connect to the database and use source. You can set the encoding format to gbk or utf before. -8 and so on, so that there will be no Chinese garbled characters

8. Authorization

##insert into mysql.user(User,Password) values("name",password("password"));

#GRANT ALL PRIVILEGES ON *.* TO "name"@'%' IDENTIFIED BY 'password';

#flush privileges;

##show grants for 'test_group'@'%';

The above is the content of importing and exporting data from mysql remote machine, locking the table or not locking the table. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!