Export - Import mySQL Database command line

[rev.dennis]

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

 

 

 

 

[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