本文详细介绍了数据库主从同步的原理与实现过程，包括配置主服务器与从服务器的步骤，以及如何通过读写分离提高数据库性能和数据安全性。同时，提供了具体的Linux与Windows环境下搭建主从服务器的实例。
主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。
使用主从同步的好处：
- 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。
- 提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据
- 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性
主从服务器的搭建为如下的步骤：
- 在主服务器上，必须开启二进制日志机制和配置一个独立的ID
- 在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
- 在开始复制进程前，在主服务器上记录二进制文件的位置信息
- 如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
- 配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置
部署linux
--备份linux上的数据库
python@ubuntu:~/Desktop$ mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql
python@ubuntu:~$ find master_db.sql
master_db.sql
python@ubuntu:~$ cp master_db.sql /home/python/Desktop
--配置主从服务器的log日志 主从服务器的server-id 不可相同
--创建从服务器用户
python@ubuntu:~$ mysql -uroot -pmysql
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
--windows连接需要File名 与 Position的值
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000097 | 700 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.09 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| jing_dong |
| mysql |
| performance_schema |
| python_test |
| python_web |
| stock_db |
| sys |
| test |
+--------------------+
9 rows in set (0.01 sec)
--用作主从服务器搭建后测试用
mysql> create database gao_master;
Query OK, 1 row affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gao_master |
| jing_dong |
| mysql |
| performance_schema |
| python_test |
| python_web |
| stock_db |
| sys |
| test |
+--------------------+
10 rows in set (0.00 sec)
部署windows （作为从服务器）
--将备份的数据库文件 拷贝到windows中 进行还原
--当前文件夹打开cmd 长按shit + 右键
D:\mysql\bin>mysql –uroot –pmysql < master_db.sql
C:\Users\Administrator>mysql -uroot -pmysql
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| areas |
| db_name |
| gao |
| jing_dong |
| mysql |
| performance_schema |
| python_test |
| python_test_1 |
| stock_db |
| sys |
| testdb |
+--------------------+
12 rows in set (0.00 sec)
mysql> use mysql
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.10 sec)
mysql> select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.11 sec)
--设置连接到master主服务器
mysql> change master to master_host='192.168.0.000', master_user='slave', master_password='slave'
,master_log_file='mysql-bin.000097', master_log_pos=000;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 2
Current database: mysql
Query OK, 0 rows affected, 2 warnings (0.77 sec)
--开启同步， 查看同步状态
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
mysql> show slave status \G:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.000
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000097
Read_Master_Log_Pos: 762
Relay_Log_File: PC-20170926RBHN-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000097
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: 762
Relay_Log_Space: 537
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: 9bcdcefb-53d8-11e6-8348-000c29409528
Master_Info_File: D:\MySql\Data\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)
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to yo
ur MySQL server version for the right syntax to use near ':' at line 1
--测试用 主从服务器 读写分离
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| areas |
| db_name |
| gao |
| gao_master |
| jing_dong |
| mysql |
| performance_schema |
| python_test |
| python_test_1 |
| stock_db |
| sys |
| testdb |
+--------------------+
13 rows in set (0.00 sec)
用户类型为localhost 只支持在本机上登录数据库 %类型可以在非本机上登录数据库
mysql -u(用户名) -p(密码) -h本机ip地址
ssh 用户名@ip地址 可以远程连接主机 进行操作
扫一扫
2469
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
