Setting up Mysql replication master / slave.

MySQL replication is to have real time two mysql databases on two identical server so they will switch if needed on the second server in case of failure of the first.

Prerequisite:

Two MySQL servers running, you can help this item if this is not the case. Beware of your Mysql versions some are not good friends.

Master:

It connects to the database server master.

mysql-h 192.168.100.1-u root-ppassword
or
mysql-u root-p (local)
It creates a user for replication:

GRANT REPLICATION SLAVE ON *.* TO downturn @ '%' IDENTIFIED BY 'retreat';
It prohibits the writing on the basics:

FLUSH TABLES WITH READ LOCK;
To set aside the ban (but not do it now: p):

UNLOCK TABLES;
It retrieves the name of the binary file, and offset. Note the we need it later to configure the slave.

SHOW MASTER STATUS;
Note the file name in the column "File" should look like normally log-bin ... and also note the number in "position".

mysql> SHOW MASTER STATUS;
+---------------------+------------+-------------- -------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+-------------- -------+--------------------------+
| Mysql-bin.003 | 73 | test, bar | foo, manual, mysql |
+---------------------+------------+-------------- -------+--------------------------+
1 row in set (0.06 sec)
If File and position are empty Take the File name as:''and for position 4. Quit with the Mysql command quit

Stop the master server:

service mysql stop
or
invoke-rc mysql stop
Edit the my.cnf file which is normally found in / etc /:

Add the following lines:

log-bin
server-id = 1
Restart the Mysql server:

service mysql start
or
invoke-rc mysql start
We now configure the slave server (192.168.100.2):

Turn off the server as above or with this command:

mysqladmin-u root-ppassword-h 192.168.100.2-P 3306 shutdown
Edit my.cnf and the server side:

Add the following lines:

server-id = 2
master-host = 192.168.100.1
master-user = fold
master-password = fold
master-port = 3306
Restart the server Mysql slave as above for the master server.

It connects to the mysql prompt:

mysql-u root-h 192.168.1O0.2 ppassword-P-3306
or
mysql-u root-p
We exchange data like this if you had a file name and position:

CHANGE MASTER TO
MASTER_LOG_FILE =''
MASTER_LOG_POS =;
If you did not file name and position by the SHOW MASTER STATUS on the master, type the following:

CHANGE MASTER TO
-> MASTER_LOG_FILE =''
-> MASTER_LOG_POS = 4;
One starts the slave
START SLAVE;
If everything is conducted correctly, you should see something like this:

SHOW SLAVE STATUS;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.1
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Server-bin.000001
Read_Master_Log_Pos: 98
Relay_Log_File: ServerB-relay binlog.000002
Relay_Log_Pos: 238
Relay_Master_Log_File: Server-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 238
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
And with SHOW PROCESSLIST:

SHOW PROCESSLIST;
+----+-------------+----------------------------+- -----+---------+------+--------------------------- -------------------------------------------+------ ------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+----------------------------+- -----+---------+------+--------------------------- -------------------------------------------+------ ------------+
| 6 | system user | | NULL | Connect | 1944 | Waiting for master to send event | NULL |
| 7 | system user | | NULL | Connect | 1944 | Has read all relay log; waiting for the slave I / O thread to update it | NULL
| 8 | admin | serverB.lan: 45,148 | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
+----+-------------+----------------------------+- -----+---------+------+--------------------------- -------------------------------------------+------ ------------+
3 rows in set (0.00 sec)
If replication errors, we check the status of the slave server:

mysql-u root-ppassword-h 192.168.100.2-P 3306
SHOW SLAVE STATUS;
Sometimes some queries on the master succeed but fail on the slave. This should not happen if you take the good backup of the master, and you never changed the data on the slave server, except by replication.

If you see an error for the 2 following lines:

The error:
Last_Error:
Here's how:

On the master:

mysql-u root-ppassword-h 192.168.100.1-P 3306
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Note the file name in the column "File" should look like normally log-bin ... and also note the number "Position".

Dump the database:

mysqldump-u root-ppassword-r / root / dump.sql nomdelasauvegarde
Send the dump file on the master server to slave server:

scp / root / dump.sql : / root
On the Slave, it stops writing:

mysql-u root-ppassword-h 192.168.100.2-P 3306
FLUSH TABLES WITH READ LOCK;
exit;
It includes the dump of the master in the basic nomdelabase "

mysql-h 192.168.100.2-u root-P-3306 ppassword nomdelabase </ root / dump.sql
It defines the correct binary master and it restarts the Slave:

mysql-u admin-ppassword-h 192.168.100.2-P 3306
CHANGE MASTER TO
-> MASTER_LOG_FILE =''
-> = MASTER_LOG_POS POSITION_FICHIER_LOG;
START SLAVE;
Normally, replication is accurately assessed.

In case of crash MASTER - Upgrading the master and replication:

We'll start with the fact that the MASTER is dropped, you have upgraded, reinstalled vTiger. We will now give the MySQL database and resume replication. At this moment, is THE SLAVE baseline. So let's start by stopping the slave, create a dump of Slavic replicate the MASTER, and restart everything.

On the slave server: We check its status:

mysql-u root-ppassword-h 192.168.100.2-P 3306
FLUSH TABLES WITH READ LOCK;
It creates a dump of the Slav

mysqldump-u root-ppassword-h 192.168.100.2-P 3306-r / root / dump.sql nomdelabase
We send the dump on the master:

scp / root / dump.sql : / root
On the master server:

mysql-h 192.168.100.1-u root-P-3306 ppassword
It creates a user for replication:

GRANT REPLICATION SLAVE ON *.* TO downturn @ '%' IDENTIFIED BY 'retreat';
We stop writing on the basis of:

FLUSH TABLES WITH READ LOCK;
It injects the dump on the slave master:

mysql-h 192.168.100.1-u admin-P-3306 ppassword nomdelabase </ root / dump.sql
It retrieves the name of the binary file, and offset. We must note them because we need it later to configure the slave.

mysql-u root-ppassword-h 192.168.100.1-P 3306
SHOW MASTER STATUS;
Note the file name in the File column which should look like normally log-bin ... and also note the position number.

Example:

SHOW MASTER STATUS;
+---------------------+------------+-------------- -------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+------------+-------------- -------+--------------------------+
| Mysql-bin.003 | 73 | test, bar | foo, manual, mysql |
+---------------------+------------+-------------- -------+--------------------------+
1 row in set (0.06 sec)
exit
Shut down the database:

mysqladmin-u root-ppassword-h 192.168.100.1-P 3306 shutdown
Edit the my.cnf file:

Add the following lines:

log-bin
server-id = 1
Restart the Mysql database

ON THE SLAVE

We will now re-synchronize the slave with the master.

It redefines the correct binary master and it restarts the Slave:

mysql-u root-ppassword-h 192.168.100.2-P 3306
CHANGE MASTER TO
MASTER_LOG_FILE =''
MASTER_LOG_POS = POSITION_FICHIER_LOG;
START SLAVE;
Normally, replication is left!

You can check the condition of the slave server with the commands SLAVE STATUS and SHOW PROCESSLIST.

Tested with version 5.1 and 5.4