MySQL主从复制 - 入门
2018-05-29 本文已影响10人
诺之林
目录
主MySQL
主MySQL环境基于Ubuntu1404
安装MySQL
sudo apt update
sudo apt install -y mysql-server
sudo service mysql status
配置MySQL
sudo sed -i "s/127.0.0.1/0.0.0.0/g" /etc/mysql/my.cnf
sudo sed -i "s/#server-id/server-id/g" /etc/mysql/my.cnf
sudo sed -i "s/#log_bin/log_bin/g" /etc/mysql/my.cnf
sudo service mysql restart
账号和状态
mysql> GRANT replication slave, replication client on *.* to replic_user identified by 'replic_pwd';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从MySQL
从MySQL环境基于Ubuntu1604
安装MySQL
sudo apt update
sudo apt install -y mysql-server
sudo service mysql status
配置MySQL
sudo sed -i "s/127.0.0.1/0.0.0.0/g" /etc/mysql/mysql.conf.d/mysqld.cnf
sudo sh -c "echo 'server-id = 2' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
sudo sh -c "echo 'relay_log = /var/log/mysql/mysql-replay.log' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
sudo sh -c "echo 'read_only = 1' >> /etc/mysql/mysql.conf.d/mysqld.cnf"
sudo service mysql restart
主从复制
mysql> CHANGE MASTER TO
-> MASTER_HOST='47.75.67.227',
-> MASTER_PORT=3306,
-> MASTER_USER='replic_user',
-> MASTER_PASSWORD='replic_pwd',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=107;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> START slave;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 47.75.67.227
Master_User: replic_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 274
Relay_Log_File: mysql-replay.000002
Relay_Log_Pos: 471
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 274
Relay_Log_Space: 675
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
测试
主MySQL
mysql -u root -p
mysql> CREATE database test;
Query OK, 1 row affected (0.00 sec)
mysql> USE test;
Database changed
mysql> CREATE TABLE machine (id int unsigned NOT NULL AUTO_INCREMENT, machine_name char(64) DEFAULT NULL, PRIMARY KEY (id));
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO machine (machine_name) VALUES ("machine 001");
Query OK, 1 row affected (0.01 sec)
从MySQL
mysql -u root -p
mysql> SHOW databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE test;
Database changed
mysql> SHOW tables;
+----------------+
| Tables_in_test |
+----------------+
| machine |
+----------------+
1 row in set (0.00 sec)
mysql> select * from machine;
+----+--------------+
| id | machine_name |
+----+--------------+
| 1 | machine 001 |
+----+--------------+
1 rows in set (0.00 sec)
