Changes between Initial Version and Version 1 of MySqlReplication

Timestamp:

Oct 13, 2010, 3:03:04 PM (16 years ago)

Author:

Serge CHASTEL

Comment:

--

MySqlReplication

@@ +1 @@
+This page describes the replication of a MySql server database.
+
+= Objectives =
+The database named {{ps1sc}} is present on the host {{neverland}}. We want to replicate it on the host named {{biglefts}}. {{neverland}} will be the master, {{biglefts}} its slave.
+
+= Preliminary =
+The master MySql version is 5.1.41-3ubuntu12.6.
+The slave one is 5.1.37-1ubuntu5.4-log.
+Warning: two mysql installation are present on biglefts. Use the one in /usr/bin, not the one in /usr/local/bin
+
+= Dump and copy =
+{{{
+shell prompt> mysqldump ps1sc > ps1sc.sql
+shell prompt> mysql
+  mysql prompt> create database ps1sc_copy;
+  mysql prompt> exit
+shell prompt> cat ps1sc.sql | mysql ps1sc_copy
+}}}
+
+= Set up the master configuration for replication =
+All commands are executed on the master ({{neverland}})
+Documentation is at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterbaseconfig.html
+* Stop the mysql server: {{sudo service mysql stop}}
+* Note: {{ps waux | grep mysqld}} should not show anything (except the grep part maybe)
+* Edit the mysql configuration and add (or uncomment+modify) the {{[mysqld]}} section so that it contains:
+{{{
+log-bin=/var/log/mysql/mysql-bin.log
+server-id=2010101301
+}}}
+* Since we are going to use InnoDB, I followed the advice in documentation and added:
+{{{
+innodb_flush_log_at_trx_commit=1
+sync_binlog=1
+}}}
+* Start the mysql server: {{sudo service mysql start}}
+
+= Set up the slave configuration for replication =
+All commands are executed on the slave ({{biglefts}})
+Documentation is at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-slavebaseconfig.html
+* Stop the mysql server: {{sudo service mysql stop}}
+* Note: {{ps waux | grep mysqld}} should not show anything (except the grep part maybe)
+* Edit the mysql configuration and add (or uncomment+modify) the {{[mysqld]}} section so that it contains:
+{{{
+server-id=2010101302
+}}}
+* I also activated binary logging (see the last paragraph in the documentation)
+{{{
+log-bin=/var/log/mysql/mysql-bin.log
+}}}
+* Start the mysql server: {{sudo service mysql start}}
+
+= Create a User for Replication =
+Documentation is at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-repuser.html
+On the master:
+{{{
+  mysql prompt> CREATE USER 'repl'@'biglefts.ifa.hawaii.edu' IDENTIFIED BY 'biglefts_repl';
+  mysql prompt> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'biglefts.ifa.hawaii.edu';
+  mysql prompt> FLUSH PRIVILEGES;
+  mysql prompt> exit
+}}}
+
++ Obtaining the Replication Master Binary Log Coordinates
+Documentation is at http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterstatus.html
+
+On the master:
+{{{
+  mysql prompt> FLUSH TABLES WITH READ LOCK;
+  mysql prompt> SHOW MASTER STATUS;
++------------------+----------+--------------+------------------+
+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
++------------------+----------+--------------+------------------+
+| mysql-bin.000001 |    83959 |              |                  |
++------------------+----------+--------------+------------------+
+1 row in set (0.00 sec)
+}}}
+In a '''different''' shell (do not disconnect from mysql client), make a dump of the database:
+{{{
+shell prompt> mysqldump --master-data ps1sc > ps1sc.db
+}}}
+Then, release the lock:
+{{{
+  mysql prompt> UNLOCK TABLES;
+}}}
+
+= Set up the slave =
+Copy the dump to the slave (with scp, rsync...). Create the database on the slave and ingest the dump:
+{{{
+mysql prompt> CREATE DATABASE ps1sc;
+}}}
+{{{
+shell prompt> cat ps1sc.db | mysql ps1sc
+}}}
+Set up the master configuration (on the slave):
+{{{
+CHANGE MASTER TO MASTER_HOST='neverland.ifa.hawaii.edu', MASTER_USER='repl', MASTER_PASSWORD='biglefts_repl', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=83959;
+}}}
+
+= Enjoy... =
+It might be necessary to tell the slave to ignore some updates:
+/usr/sbin/mysqld --replicate-ignore-table=mysql.user