Jump to content

Export - Import mySQL Database command line


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 post
Share on other sites
  • 3 weeks later...

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 post
Share on other sites
×
×
  • Create New...