MySQL master-slave copy steps

MySQL Master-slave copy steps

1 Master-slave server turn on time synchronization

[Email protected] ~]# ntpdate 172.18.0.1

2 Restore Operations

first, determine whether the master server has data when building the master-slave replication schema. If there is data on it back up after the recovery from the server. If the master-slave architecture is built from 0 onwards. You can skip this step. The starting point for master-slave replication is the log file where the primary node is located and its event location when the backup operation occurs.

Note: From the server's MySQL the version needs to be the same or higher than the primary server version

3 Configuring the primary database

[Email protected] ~]# VIM/ETC/MY.CNF [Mysqld] Log_bin =/var/log/mariadb/mysql-bin Server-id = 1 Innodb_file_per_table = On Skip_name_resolve = On

Log_bin: The parameter followed by the file path indicates that the binary log is turned on, and the path to the MySQL user must have read and write permissions. It is recommended that the data directory be stored separately and placed on the partitions of different disks to reduce the damage rate.

Server-id: in the master from the synchronization schema, the Server-id of the primary server is 1and the server is not 1.

Innodb_file_per_table: followed by on or 1 for setting Innodb to stand-alone tablespace mode, use Innodb after setting this parameter Each table in each database of the engine creates a separate tablespace, one that has its own characteristics: each table has its own separate table space, and the data and indexes for each table are stored in its own table space. A single table can be moved between different databases (single table import, export), space can be reclaimed (except for the drop table operation, tablespace cannot be reclaimed by itself).

Skip_name_resolve: Skip using DNS domain name resolution.

Start the service (if the database is in use and open Binlog does not restart)

[Email protected] ~]# systemctl restart MARIADB

To add a master-slave replication User:

MariaDB [(None)]> GRANT REPLICATION slave,replication CLIENT on * * to [e-mail protected] ' 192.168.10.% ' identified by ' Copypass '; MariaDB [(None)]> FLUSH privileges;

4  config from database

< P>[[email protected]  ~]# vim/etc/my.cnf [mysqld] server-id=2 relay_log=relay-log relay_log_index=relay-log.index read_ Only=1 innodb_file_per_table=1 skip_name_resolve=1

DataDir: Specify the directory where the data resides

Server-id can not be 1, when a master more from when, its Server-id also best different

Relay_log: The heel path indicates that the trunk log is turned on, and relative paths are used to indicate that the data directory is in the same directory.

READ_ONLY: read-only from the server. The other is consistent with the primary server.

Start the service:

[Email protected] storage1 ~]# systemctl start mariadb

Establish a connection to the primary server

, Master_user=copyuser,    -Master_password=copypass,    -master_log_file= ' mysql-bin.000005 ', -> master_log_pos=24622 ;

Master_log_file, Master_log_file is the transaction point in time at which the backup was due. Can be found in the Xtrabackup_info file under the backup directory used in recovery .

[[email protected] storage1 ~]# grep ' binlog_pos ' 2017-11-13_20-29-22/xtrabackup_info Binlog_pos = filename 'mysql-bin.000005', position '24622'

of course, if the master-slave replication architecture is built from 0 onwards. You need to use the following command on the primary server to view:

[[email protected] ~]# MySQL MariaDB [(None)]> Show Master status; +------------------+----------+--------------+------------------+ | File |  Position | binlog_do_db | binlog_ignore_db | +------------------+----------+--------------+------------------+ |    mysql-bin.000005 |              49652 |                  | | +------------------+----------+--------------+------------------+ 1 row in Set (0.00 sec)

Open a Io_thread Threads

MariaDB [(None)]> START SLAVE;

START SLAVE [io_thread| Sql_thread]; # Start the replication thread , stop using stop

Important points to note in the master-slave replication architecture:

1 should be limited from the server to read-only

set read_only=on on the service, which is not valid for users with SUPER privileges

how to block all users:

mysq> FLUSH TABLES with READ Locak; # do not exit (use when testing)

2 How to ensure that the master-slave copy of things safe?

When the primary node is enabled for parameters:Sync_binlog=on, which is encountered when a thing is committed, the events recorded in the Bin-log buffer must be immediately written to the binary log file on disk to notify the node to replicate the synchronization. You can also use Sysnc_binlog=n to turn this option on, and when n transactions commit, MySQL executes a disk synchronization command such as Fsync to write the contents of the buffer to disk. When n=0, it means that when a transaction commits, only the data in Binlog_cache is written to the Bin_log file, not disk synchronization, which is the best performance, but does not guarantee transaction security

If you are using the InnoDB storage Engine:

innodb_flush_logs_at_trx_commit= 1    # write the things in memory immediately to the log of things on disk.

innodb_support_xa= on # xa : Distributed things, that is, whether to let InnoDB support Distributed things, distributed submissions

in the SLAVE node:

skip_slave_start= on    # Turn off auto-commit. Choose manual start to avoid errors

It can be opened when necessary. # mariadb[(none)]> show variables like "%relay_log%";

Master node:

Sync_slave_start = on

from node:

Sync_relay_log

Sync_relay_log_info

Faults encountered:

Error:datadir must be specified appears in the Copy-back process using a backup to recover from a file.

Fault Description:

Note that the data recovery directory could not be found when recovering, just specify the database file directory in the MARIADB configuration file

Workaround:

Add the DataDir parameter to the configuration file.

This article is from the "Keep Simple Keep Stupid" blog, make sure to keep this source http://yangzhiheng.blog.51cto.com/11586378/1981493

MySQL master-slave copy steps