Docker mysql master-slave configuration details and instances, dockermysql

Docker mysql master-slave configuration details and instances, dockermysql

Docker mysql master-slave Configuration

1. First create two file my-m.cnf (master database configuration), my-s.cnf (slave database configuration)

The my-m.cnf content is as follows

# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; version 2 of the License.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA## The MySQL Community Server configuration file.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html[client]port    = 3306socket   = /var/run/mysqld/mysqld.sock[mysqld_safe]pid-file  = /var/run/mysqld/mysqld.pidsocket   = /var/run/mysqld/mysqld.socknice    = 0[mysqld]user    = mysqlpid-file  = /var/run/mysqld/mysqld.pidsocket   = /var/run/mysqld/mysqld.sockport    = 3306basedir   = /usrdatadir   = /var/lib/mysqltmpdir   = /tmplc-messages-dir = /usr/share/mysqlexplicit_defaults_for_timestamplog-bin = mysql-bin server-id = 1 # Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.#bind-address  = 127.0.0.1#log-error = /var/log/mysql/error.log# Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# * IMPORTANT: Additional settings that can override those from this file!#  The files must end with '.cnf', otherwise they'll be ignored.#!includedir /etc/mysql/conf.d/

These two lines are used. You only need to add them in the original configuration.

Log-bin = mysql-bin
Server-id = 1

The my-s.cnf content is as follows

# Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.## This program is free software; you can redistribute it and/or modify# it under the terms of the GNU General Public License as published by# the Free Software Foundation; version 2 of the License.## This program is distributed in the hope that it will be useful,# but WITHOUT ANY WARRANTY; without even the implied warranty of# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the# GNU General Public License for more details.## You should have received a copy of the GNU General Public License# along with this program; if not, write to the Free Software# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA## The MySQL Community Server configuration file.## For explanations see# http://dev.mysql.com/doc/mysql/en/server-system-variables.html[client]port    = 3306socket   = /var/run/mysqld/mysqld.sock[mysqld_safe]pid-file  = /var/run/mysqld/mysqld.pidsocket   = /var/run/mysqld/mysqld.socknice    = 0[mysqld]user    = mysqlpid-file  = /var/run/mysqld/mysqld.pidsocket   = /var/run/mysqld/mysqld.sockport    = 3306basedir   = /usrdatadir   = /var/lib/mysqltmpdir   = /tmplc-messages-dir = /usr/share/mysqlexplicit_defaults_for_timestamplog-bin = mysql-bin server-id = 2# Instead of skip-networking the default is now to listen only on# localhost which is more compatible and is not less secure.#bind-address  = 127.0.0.1#log-error = /var/log/mysql/error.log# Recommended in standard MySQL setupsql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# * IMPORTANT: Additional settings that can override those from this file!#  The files must end with '.cnf', otherwise they'll be ignored.#!includedir /etc/mysql/conf.d/

Similarly, the two lines

Log-bin = mysql-bin
Server-id = 2

2. OK. With the configuration file, you can start MySQL. First, start the master database.

$ Docker run-d-e MYSQL_ROOT_PASSWORD = admin -- name mysql-master-v/soft/my-m.cnf:/etc/mysql/my. cnf-p 3307: 3306 mysql

3. Start slave Database

$ Docker run-d-e MYSQL_ROOT_PASSWORD = admin -- name mysql-slave-v/soft/my-s.cnf:/etc/mysql/my. cnf-p 3308: 3306 mysql

4. Connect to the master database and run the following command to create a user for data synchronization.

$ Grant replication slave on *. * to 'backup '@' % 'identified by '123 ';

5. view the status of the master database

$ Show master status;

Remember the values of File and Position. If no data is found, check steps 1 and 2 for configuration problems.
I found mysql-bin.000004, 312

6. Connect to the slave database and run the following command to set the master database connection

$ Change master to master_host = '192. 32.32.54 ', master_user = 'backup', master_password = '2016 ',
Master_log_file = 'mysql-bin.000004 ', master_log_pos = 312, master_port = 3307;

7. Start Synchronization

$ Start slave;

8. View synchronization status

$ Show slave status

If you see Waiting for master send event... or something, your modifications to the master database will be synchronized to the slave database.

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!