Translated by: Iron anchor (Translated by: July 22, December 25, 2013): SettingUpReplication master-slave Replication involves two steps: setting on the master server (group) and setting on the slave server (Group). if the master server master is not enabled, activate the binary log. set a unique
Source article: Setting Up Replication master-slave Replication contains two steps: settings on the master server (group) and settings on the slave server (Group). if the master server master is not enabled, activate the binary log. set a unique
Translated by: Tie
Translated on: February 1, December 25, 2013
Source article: Setting Up Replication
Master-slave Replication involves two steps: setting on the master server (group) and setting on the slave server (group.
Configure master server
If it is not enabled, You need to activate the binary log. Set a unique server_id for the master, and set server_id for all slave servers. The server_id value can be an integer number (1 ~ 2 ^ 31-1), The server_id of each server in the same replication group must be unique. the slave server of slave must have the permission to connect to and copy data from the master. generally, a single user is created for each slave, and only the REPLICATION permission (replication slave permission) is granted ).
Example
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'slave_host' IDENTIFIED BY 'bigs3cret'; FLUSH PRIVILEGES;
Note that some system configuration options may affect master-slave replication. Check the following variables to avoid problems:
Skip-networkingIf "skip-networking = 1", the server will be limited to be connected only by localhost, to prevent other machines from remotely connecting to this server.
Bind_addressSimilarly, if the server only listens to the TCP/IP connection of 127.0.0.1 (localhost), the remote slave cannot connect to the server.
Configure slave server slave
Specify a unique server_id for slave. The server_id. server_id value must be an integer number (1 ~ 2 ^ 31-1), The server_id of each (/) server in the same replication group must be unique. to make this configuration item take effect, restart the service.
Obtains the binary log coordinates of the master node.
When viewing the position (location) of the current binary log, you need to stop any database submission. You can use this coordinate point to tell the slave server to Start copying from a specific point.
Execute the command "flush tables with read lock" on the master to force log writing and LOCK all TABLES. During this period, the session cannot be closed and the LOCK will be released once the session is closed. run "show master status" to obtain the current location information of binary logs. write down the File and Position information. if the binary log feature is enabled just now, it is null. then, Start copying data from the master to the slave. refer:
Backup, Restore and ImportAfter the data is copied from the master to the slave, you can execute "unlock tables;" to release the lock on the master.
Example: -- show master status; + metric + ---------- + -------------- + ---------------- + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + usage + | mariadb-bin.000096 | 568 | + usage + -------- + -------------- + ------------------ + --
Start Slave server Slave
After the data is imported, you can start the copy function. Run"
CHANGE MASTER"To start.
Make sure"
MASTER_LOG_FILE", And"
MASTER_LOG_POS"It is consistent with the information written down by the master early.
Example:
CHANGE MASTER TO MASTER_HOST='master.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='bigs3cret', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000096', MASTER_LOG_POS=568, MASTER_CONNECT_RETRY=10;
Then, start slave
START SLAVE;
View the slave status:
SHOW SLAVE STATUS;