The values mysql-bin.000001 and 613Ĭome from the output of the SHOW MASTER STATUS that you ran earlier. Query OK, 0 rows affected, 2 warnings (0.01 sec)Īt this point, the slave is in sync with the master and ready to accept and Query OK, 0 rows affected, 1 warning (0.00 sec) Import the dump file you created at the master and copied over to this slave: $ mysql -u root -p STOP SLAVE 2) Restart the MySQL service for changes to take effect: sudo systemctl restart mysql 3) Import the db dump: It also allows this slave to act as the master for another Logging is not strictly necessary, it makes sense to keep the slaveĬonfiguration as close to the master’s as possible, in case the slave is It server-id (this time to 2), and enables binary logging. Like for the master above, this makes the slave listen on the private IP, sets In file /etc/mysql//mysqld.cnf, uncomment or set the following: bind-address = 10.0.0.157 server-id = 2 log_bin = /var/log/mysql/mysql-bin.log Transfer the dump file securely to the slave, for e.g., using scp: $ scp dbdump.sql 10.0.0.157:/tmp Slave setup 1) Update the configuration: You can do this either by exiting your mysql CLI shell, or by running: mysql> UNLOCK TABLES 8) Copy the file to the slave: Note: you can also stop mysqld and transfer the db files. 6) Dump the master db: $ mysqldump -u root -p -all-databases -master-data > dbdump.sql This information will be needed to initialize the slave in a later step. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 5) Note down master replication log position: mysql> SHOW MASTER STATUS The lock needs to remain in place until the Note that this lock is released either when you exit the mysql CLI client, or Next we need to lock the master to get a consistent snapshot to bootstrap the Mysql> GRANT REPLICATION SLAVE ON *.* TO OK, 0 rows affected (0.00 sec) 4) Lock the master: Mysql> CREATE USER IDENTIFIED BY 'slavepass' TheĪccount needs REPLICATION SLAVE privilege. We’ll set the slave up to connect to the master via a dedicated account. 2) Restart the MySQL service for changes to take effect: sudo systemctl restart mysql 3) Create a user for replication: Must be unique for each node among a set of connected master and slave nodes) toġ, and enables binary logging. This makes the master listen on the private IP, sets it server-id (which In the file /etc/mysql//mysqld.cnf uncomment or set the following: bind-address = 10.0.0.156 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log This means theĭatabase will be read-only and all writes will be blocked for the duration of Of the database, which involves placing a global read lock. This process needs to take a consistent snapshot Which can talk to each other over a private network, and that the nodes have theįirst let’s setup the master. The rest of the post assumes you have setup two identical nodes running MySQL, It is only a matter of: $ sudo apt-get install mysql-server The Ubuntu 16.04 LTS repositories come with version 5.7 of MySQL, so installing Use OpsDash to monitor the replication status. Replication is commonly used for improved availability or to have anĮxtra node to run analytic queries and reports. Here’s how to setup replication between two MySQL 5.7 servers running on Ubuntuġ6.04 LTS.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |