Mysql:
MySQL is most commonly used for Web applications and for embedded applications and has become a popular alternative to proprietary database systems because of its speed and reliability. MySQL can run on LINUX, Windows and Mac OS.
By default remote access to the MySQL database server is disabled for security reasons.
However, some time you need to provide remote access to database server from home or a web server.
Migrate Database from one server to another server remotely
1.Single Database backup :
mysqldump is a command to take backup MYSQL Database in *.sql format
# cd /opt/ (we are take backup database in /opt/ directory)
# mysqldump -u root -p[mysql-root-password] [database-name] > backupfilename.sql
# mysqldump -u root -pmahesh12 bsrtech_data > bsrtech_data_bkp.sql
where mysql-root password is "mahesh12"
database name is "bsrtech_data"
backup file name is "bsrtech_data_bkp.sql"
2.Take backup multiple databases :
Using mysqldump command, we can also take backup multiple databases
# mysqldump -u root -p[mysql-root-password] --databases [database1-name] [database1-name] > backupfilename.sql
Ex:
# mysqldump -u root -pmahesh12 --databases mahesh_data bsrtech_data bsrjobs_data > bsrdata_bkp.sql
where mahesh_data, bsrtech_data and bsrjobs_data are three databases
Verify the three databases in backup file "bsrdata_bkp.sql"
# grep -i "database:" /opt/bsrdata_bkp.sql
Database: mahesh_data
Database: bsrtech_data
Database: bsrjobs_data
3. To take backup all databases :
Using mysqldump command to take entire database in ur server
# mysqldump -u root -pmahesh12 --all-databases > /opt/server_database_bkp.sql
where "mahesh12" is a password of mysql root user, and take total database in /opt/ directory.
4. To take backup a specific table :
using "mysqldump" command to take backup specific table in a selected database.
# mysqldump -u root -ppassword database_name table_name > /opt/backup_filename.sql
# mysqldump -u root -pmahesh12 bsrtech_data contact_numbers > /opt/contacts_table.sql
where "bsrtech_data" is a database, "contact_numbers" is a table in this database and "/opt/contacts_table.sql" is a backup file
5. How to restore mysql database :
connect mysql and using " < " symbol to restore backup database
In this example restore "bsrtech_data" database, When you are restore the "bsrdata_bkp.sql" backup database,
make sure to create the "bsrtech_data" databse before you can restore the database.
# mysql -u root -pmahesh12
mysql> create database bsrtech_data;
Query OK, 1 row affected (0.02 sec)
# mysql -u root -ppassword database < backup_filename.sql
# mysql -u root -pmahesh12 bsrtech_data < /opt/bsrdata_bkp.sql
where "mahesh12" is a mysql-root password, "bsrtech_data" is a database, and "/opt/bsrdata_bkp.sql" is backup database.
No comments:
Post a Comment