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:
mysqldump -h 127.0.0.1 -u name -p dbname >name.sql
Export the database dbname of 127.0.0.1 to the name.sql file
mysqldump -h 127.0.0.1 -u username -ppassword dbname tablename >name.sql
Export the tablename table in the database dbname on ip 127.0.0.1 to the name.sql file
mysqldump -h 127.0.0.1 -u username -ppassword dbname tablename --where="id>10 limit 10000" >name.sql
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
mysqldump -h 127.0.0.1 -u username -ppassword --single-transaction dbname tablename>name.sql
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
mysqldump -h 127.0.0.1 -u username -ppassword --single-transaction -d dbname tablename>name.sql
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.
mysqldump -h 127.0.0.1 -u username -ppassword --single-transaction -t dbname tablename>name.sql
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
use dbname; set names gbk; source /d1/name/name.sql;
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
|
##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 (m.sbmmt.com)! |