Create, Backup and Restore MYSQL Database in Linux

Spread the love
  • 4
    Shares

Create new database using terminal

[root@localhost]# mysql -u root -p
mysql> create database 1234;
mysql> grant all privileges on web.* to web@localhost identified by “1234”;
mysql> show datadbase;

Backup database using terminal

[root@localhost]#mysql -u root -p 123 > /root/Desktop/1234.sql
Note :- In Backup ” >” greater than symbol is used.

Restore database using terminal

restore:# mysql -u root -p[root_password] [database_name] < dumpfilename.sql

mysql –u root –p Passw0rd ocs < /root/Desktop/
mysql –u root –p Passw0rd ocs < /root/Desktop/ 1234.sql

[root@localhost]#mysql -u root -p 1234 < /root/Desktop/1234.sql
Note :- In Restore”<” lesser than symbol is used.

One thought on “Create, Backup and Restore MYSQL Database in Linux

  • June 30, 2017 at 12:02 am
    Permalink

    First, I’ve never tried `mysql -u root -p 1234` to backup, but I imagine since that usually gives you a prompt, using `mysqldump -u root -p –databases db_name_to_dump` will probably give you better results.

    In those backups you can zip then in-line as well saving a lot of disk space and possibly time if you are writing to an NFS or iSCSI mount.

    ### Backups
    Instead of: `mysql -u root -p 1234 > /root/Desktop/1234.sql`

    do: `mysqldump -u root -p –databases 1234 | gzip > /root/Desktop/1234.sql.gz`

    ### Restores
    Instead of: `mysql –u root –p Passw0rd ocs < /root/Desktop/1234.sql`

    do: `zcat /root/Desktop/1234.sql.gz | mysql –u root –p Passw0rd ocs`

    Using pipe instead of redirects like that also gives you other options such as cloning dbs/tables to other dbs on the same machine or a different one without file intermediaries:

    `mysqldump -u root -p –databases 1234 | mysql –u root –p Passw0rd ocs`

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *