mysql replication

Mysql server replication

#apt-get install mysql-server mysql-client

here it will prompt for the new password

#/etc/mysql/my.cnf

IN MASTER:

52 bind-address = 192.168.1.26

77 general_log_file = /var/log/mysql/mysql.log

78 general_log = 1

79

80 log_error = /var/log/mysql/error.log

81

90 server-id = 1

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

92 expire_logs_days = 10

93 max_binlog_size = 100M

94 binlog_do_db = exleaz

sudo apt-get install mysql-server

Start another instance and repeat the above steps, this will be our slave server.

Since now the MySQL server is running, let’s configure it to make this the Master server.
Edit

#/etc/mysql/my.cnf

MySQL should listen to all IP Addresses, so we comment out the following lines:

#skip-networking
#bind-address = 127.0.0.1

We need to specify the database that needs to be replicated, the path to the binary log (slaves read this log to know what changed in the master and update themselves accordingly) and set a server id to make this as the master server.

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=testdb
server-id=1

Restart MySQL by issuing the command
#/etc/init.d/mysql restart
Log in to the MySQL shell

#mysql -u root -p

Inside the shell run the following pair of commands to grant replication privileges to the slave user

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<a_real_password>';
use testdb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

After running the above command, you should be able to see binary log position

Write down the position, this would be needed later. Leave the shell.

Quit;

The existing data on the master would have to be manually moved to the slave table. Hence, we take a database dump of testdb using mysqldump

mysqldump -u root -p<password> --opt testdb > testdbdump.sql
Transfer the dump file to the slave server.

Next, we need to unlock the tables in testdb

mysql -u root -p
UNLOCK TABLES;
quit;

Our master server has been configured, lets ready the slave now.

Create a database with the same name, testdb in our case

mysql -u root -p
CREATE DATABASE testdb;
quit;

Its time to load the sqldump file created earlier
mysql -u root -p<password> testdb < /path/to/testdbdump.sql
To configure the slave, edit its

my.cnf

server-id = 2
master-host=
master-user=slave_user
master-password=password
master-connect-retry=60
replicate-do-db=testdb
Restart MySQL/etc/init.d/mysql restart

Final steps

mysql -u root -p
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST=' ', MASTER_USER='slave_user', MASTER_PASSWORD=' ',
MASTER_LOG_FILE='mysql-bin.001', MASTER_LOG_POS=315;

MASTER_HOST is the private IP of the master, you can copy this from the instance details pane.
MASTER_USER is the user we granted replication privileges on the master
MASTER_PASSWORD is the password of MASTER_USER on the master
MASTER_LOG_FILE is the name of the binary log file on the master
MASTER_LOG_POS is the position of the binary log

Finally, start the slave

START SLAVE;
quit;

And now, each write to the master gets instantly replicated on the slave as well. You can create and configure multiple slaves and all of them will have the same data as on the master.

In Client :

90 server-id = 2

91 master-host = 192.168.1.26

92 master-user = venky

93 master-password = exleaz123

94 master-connect-retry = 60

95 replicate-do-db = exleaz

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

97 expire_logs_days = 10

98 max_binlog_size = 100M

Adding multile databases to replication:click here

=============================
MASTER: add lines to my.cnf
=============================
binlog-do-db=database_name_1
binlog-do-db=database_name_2
binlog-do-db=database_name_3 

MASTER: SQL SYNTAX
=============================
GRANT REPLICATION SLAVE ON *.* TO 'user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
FLUSH TABLES WITH READ LOCK;
UNLOCK TABLES;
SHOW MASTER STATUS;
output> file | Position | Binlog_Do_DB
mysql-bin.000963 1570 database_name_1,database_name_2,database_name_3
=============================
SLAVE: add lines to my.cnf
=============================
replicate-do-db=database_name_1
replicate-do-db=database_name_2
replicate-do-db=database_name_3
=============================
SLAVE: SQL SYNTAX
=============================
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.2', MASTER_USER='user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000963', MASTER_LOG_POS=98;
START SLAVE;
SHOW SLAVE STATUS; 

NOTE:

MASTER_LOG_FILE='mysql-bin.000963', MASTER_LOG_POS=98; is displayed when you run the SQL command from the master: cmd mysql#> SHOW MASTER STATUS;

ALSO:

When you run #> SHOW SLAVE STATUS;
make sure you see: Slave_IO_Running | Slave_SQL_Running
Yes Yes
for more in details

mysql replication on amazon ec2

mysql forum

master failover