MySQL Commands, Administration, Backup, Restore

 

SOURCE : STACKOVERFLOW 

To export If it’s an entire DB, then: 

mysqldump -u [uname] -p[pass] db_name > db_backup.sql

If it’s all DBs, then: 

mysqldump -u [uname] -p[pass] --all-databases > all_db_backup.sql

If it’s specific tables within a DB, then: 

mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql

You can even go as far as auto-compressing the output using gzip (if your DB is very big): 

mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz

If you want to do this remotely and you have the access to the server in question, then the following would work (presuming the MySQL server is on port 3306): 

mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql

To import Type the following command to import sql data file: 

mysql -u username -p -h localhost DATA-BASE-NAME < data.sql

In this example, import ‘data.sql’ file into ‘blog’ database using sat as username: 

mysql -u sat -p -h localhost blog < data.sql

If you have a dedicated database server, replace localhost hostname with with actual server name or IP address as follows: 

mysql -u username -p -h 202.54.1.10 databasename < data.sql

OR use hostname such as gautamthakur.net 

mysql -u username -p -h gautamthakur.net database-name < data.sql

If you do not know the database name or database name is included in sql dump you can try out something as follows: 

mysql -u username -p -h 202.54.1.10 < data.sql

Popular posts from this blog

WordPress Themes and Plugins Installation Problem

Image
WordPress plugins and themes installation problem Step 1 – Add the following line at the end of wp-config.php file define( 'FS_METHOD' , 'direct' ); Step 2 – Give 777 recursive permission to wp-content folder chmod -R 777 wp-content Most probably, this will fix all the issues of yours. But in case, you have bought and theme and plugins from some website like themeforest of other site and your theme or size is more than the size mentioned in php.in file. Then you need to increase the size of followings – upload_max_size post_max_size max_execution_size etc. and restart the apache service OR Best Solution: Simply give www-data user to user and group onwership to website serving directory
Read more

How to Convert PEM KeyFile into PPK KeyFile and vice versa?

Image
Hi, In this post, we will learn that how can we convert PEM KeyFile into PPK KeyFile and vice versa including the installation of the tool which we will use to accomplish this. For the Windows Operating System In Windows system is easy and straight forward. You just need a tool called "puttygen.exe" from the URL -  https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html Choose the appropriate version as per your OS. Convert PEM into PPK in Windows After downloading the PuTTYgen and open it, load your file (select All Files (*.*) in the bottom dropdown to select your PEM key file).  You will get a message Successfully imported foreign key, Just click OK, Now click on Save private key button to save that key in the location where do you want to save it. Convert PPK into PEM in Windows Now we will convert PPK file into PEM using PuTTYgen in Windows PC Open the PuTTYgen tool, now load your PPK file into it, and finally, Go to Conversions Menu and Click on Export OpenSSH k...
Read more

How to SSH EC2 instance with ssh-agent along with SSH agent forwarding to SSH private instances in VPC?

Image
            Image Source: atulhost.com and modified by me        Hi, I have taken this post from AWS Blog, and this very important to SSH into your VM using ssh-agent and SSH forwarding for EC2 private instance to SSH without storing the private key securely in Windows, Mac, and Linux.  So, I want you to using this as this is very important as per security reasons. Configuring ssh-agent The first step in using SSH agent forwarding with EC2 instances is to configure a bastion in your VPC. We suggest that the instance you use for your bastion be purpose-built and that you use it only as a bastion and not for anything else. The bastion should also be set up with a security group that’s configured to listen only on the SSH port (TCP/22).  Always remember the following when configuring your bastion: Never place your SSH private keys on the bastion instance. Instead, use SSH agent forwarding to connect first to the bastion and from t...
Read more