Jump to content

Export - Import mySQL Database command line


rev.dennis
 Share

Recommended Posts

Okay, my experience on exporting and importing mySQL Database


 


Exporting (Pretty Easy)


[root@ipsetest ~]# mysqldump -u root -p oaipse > oaipse.sql


 


 


 


Importing (not too bad but learned a couple of things)


[root@ipseweb ~]# mysql -u root -p oaipse < oaipse.sql

Enter password:

ERROR 1153 (08S01) at line 732: Got a packet bigger than 'max_allowed_packet' bytes

 

To fix ERROR 1153 perform the following otherwise your database imported successfully.


[root@ipseweb ~]# mysql -u root -p

Enter password:


mysql> set global net_buffer_length=1000000;


mysql> set global max_allowed_packet=1000000000;

mysql> exit

Rerun the import command as noted above before error

 

Verify your database imported


[root@ipseweb ~]# mysqlshow -u root -p

 


 

 




 


Link to comment
Share on other sites

  • 3 weeks later...
Guest dennis

Show Databases





SHOW DATABASES;





Create database





mysqladmin -u root -p create oaipse




Show mySQL Users





select user,host from mysql.user;





Create new user





CREATE USER 'oauser'@'localhost' IDENTIFIED BY 'secure09';





Grant Privileges





GRANT ALL PRIVILEGES ON *.* TO 'oauser'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;





TO EXPORT DB TO A FILE:





mysqldump --add-drop-table -p DATABASENAME --user=DATABASEUSER --password=DATABASEPASSWORD > /FULL/PATH/TO/ROOT/MYBACKUP.SQL






TO IMPORT DB FROM A FILE:





mysql --user=DATABASEUSER --password=DATABASEPASSWORD DATABASENAME < /FULL/PATH/TO/ROOT/MYBACKUP.SQL



Replace the uppercase words with your own values.



DELETE Database





mysql> drop database


Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...