MysqlDump - A program to take Mysql Backup

In This article we will learn What is Mysqldump? how it can be used with multiple options like add-drop-tables,d,e,w,x,f,no-tablespaces.Lets start with what is Mysqldump.

What is Mysqldump?

Mysqldump client is a utility that performs backup .It can be used to backup a database or a collection of databases for backup or transfer to another MySQL server. The dump typically contains SQL statements to create the table, populate it, or both. However, Mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

Mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used.

You can use Mysqldump in three ways:-

• db_name [tbl_name ...]
• --databases db_name ...
• --all-databases

let's see how to use all the three ways of Mysqldump:-

• db_name [tbl_name ...] : This option should be used,if you are taking backup of a single database with multiple tables.

          shell> mysqldump [options] db_name [tbl_name ...]

• --databases db_name ... :  This option should be used,If we have multiple databases to backup. 

          shell> mysqldump [options] --databases db_name ...

• --all-databases: As the name suggest,This options should be used if you need to take backup of complete database.

          shell> mysqldump [options] --all-databases

Now we will se what lies in the [ options ],Mysqldump supports multiples options which can be used but we will be discussing only few of those options here.The options which are going to discuss are given below:-

• --add-drop-table

• -d

• -e

• -w

• -x 

• -f

• --no-tablespaces

--add-drop-table: Add a DROP TABLE statement before each CREATE TABLE statement.So when every you take a backup of database or a table,It will always drop the existing table before creating the new table.It will help you in case having the same schema design.

-d or --no-data: if this option is used,No content from the table will be dumped.This option will only dump the structure of the table or tables

-e or --extended-insert: Use multiple-row INSERT syntax that include several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded. 

-w or --where: Dump only selected records. Quotes are mandatory.

-x or --xml: Dump the database/databases in the well formed XML. 

-f or --force: it will continue to take the dump of the table even if there is any error.

--no-tablespaces: If the option no-tablespaces is used, it will take not take the dump of table structure,only the content will be backup from the tables.

By: Unknown On 8:18 PM

Continue Reading

Setup Mysql Replication in Master Slave Mode

In this tutorial we will learn what is Mysql Replication,How to setup mysql replication.

Let's start with what is MySQL replication is.

MySQL replication

MySQL replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database. This can helpful for many reasons :

• Scale-out solutions
• Data Security
• Analytics
• data distribution
• Backup

 we will Setup a very simple Mysql Replication with One Master and One slave.let's take two machines for achieving the same. lets take two machines for setting up the Mysql Replication

Machine One:10.33.76.78- Master Database

Machine Two:10.22.43.46- Slave Database

Setup

In this article i am using ubuntu to setup the replication,So first thing which needs to be done for  replication is installation of mysql on the system 

apt-get install mysql-server mysql-client

Once you are done with the installation part,lets start the real fun of replication.

Step One—Setup the Master Database

On Master database we need to make couple changes in the configuration file on Mysql.

The first step is to find the section that looks like this, binding the server to the local host:

bind-address            = 127.0.0.1

Replace the standard IP address with the IP address of server.

bind-address            = 0.0.0.0

Next we need to un-comment the line which has server-id:

server-id               = 1

Now,Search for the line that refers to log_bin and uncomment the same,I will make the bin logs on default location but you can change the location of bin logs as well.

log_bin                 = /var/log/mysql/mysql-bin.log

Next option is used if you want to have Mysql Replication on the basis of databases i.e if you have multiple databases and you want Replication for only selective database

binlog_do_db            = newdatabase

We are done with all the configuration changes on Master and now it's time to restart the mysql.

service mysql restart

We need to grant privileges to the slave. The command should be in this format:

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';


'slave_user' : Denotes the name of user used for replication,It can be anything.

'%' : Denotes that with above user and password,Any host can become slave of this master,I would recommend that instead of % you should use the ip-address of the slave host.

'password': it should be combination of character numeric.

Last thing to do :


FLUSH PRIVILEGES;


Now,We need to note down the position of master and take the backup of databases for which we want to setup the Mysql Replication,Which can be done with following sequence of commands:-

USE newdatabase;

FLUSH TABLES WITH READ LOCK;


mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |   |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


The position noted above will be helpful while setting up the Slave.

export your database using mysqldump in the new window (make sure you are typing this command in the bash shell, not in MySQL).

mysqldump -u root -p --opt newdatabase > newdatabase.sql

Now, returning to your your original window, unlock the databases (making them writeable again). Finish up by exiting the shell.

UNLOCK TABLES;

QUIT;

Step Two—Configure the Slave Database

On Slave Machine,open up the MySQL shell and create the new database that you will be replicating from the master (then exit):

CREATE DATABASE newdatabase;

EXIT;

After creating the database, Now Import that you previously exported from the master database.

mysql -u root -p newdatabase < /path/to/newdatabase.sql

Now we need to configure the slave configuration :

make sure that your have the following Four criteria appropriately filled out:

server-id               = 2

relay-log               = /var/log/mysql/mysql-relay-bin.log

log_bin                 = /var/log/mysql/mysql-bin.log

binlog_do_db            = newdatabases

Server-id needs to be unique as and we have already used server-id = 1 on master,lets setup server-id=2 here on slave.

You might not find the rest of three values in my.cnf by default,So if the values are missing please add it and restart the mysql.

service mysql restart

Let's enable the replication from within the MySQL shell.Open up the the MySQL shell  and type in the details :

CHANGE MASTER TO MASTER_HOST='ip-address of master',MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='', MASTER_LOG_POS= ;

This command accomplishes several things at the same time:

It designates the current server as the slave of our master server.

It provides the server the correct login credentials

Last of all, it lets the slave server know where to start replicating from; the master log file and log position come from the numbers we wrote down previously.

With that—you have configured a master and slave server.

Activate the slave server:

START SLAVE;

You be able to see the details of the slave replication by typing in this command. The \G rearranges the text to make it more readable.

SHOW SLAVE STATUS\G

Show slave status will give you complete details which you have entered while setting up the slave.like master server,user name and etc.You can test t

he replication by simplying createing a table in the database for which you have enabled the replication and see if the same table exists on slave.If

you table is present on the slave machine as well then your Mysql Replication is working fine,Else investagte further to check what has went worng.

By: Unknown On 4:09 PM

Continue Reading