您好，登录后才能下订单哦！
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
一、MySQL复制原理。
二、MySQL复制配置。
一、MySQL复制原理
1.MySQL复制原理图
复制原理：
Slave启动IO Thread和SQL Thread
Master启动DumpThread
1.Slave通过IO Thread向Master的Dump Thread发送请求，Master的Dump Thread请求本地的binlog。
2.Master读取本地的binlog，并将读取内容发送给Slave的IO Thread线程。
3.Slave的IO Thread将收到的内容，写入到本地的relaylog中。
4.Slave的SQL Thread读取本地的relaylog文件内容。
5.Slave的SQL Thread将读取的内容写入到本地数据库。
二、MySQL复制配置
1.MySQL复制图
2.配置Master
2.1.修改Master配置文件
vim /etc/my.cnf [mysqld] #开启二进制日志文件 log-bin = mysql-bin #配置唯一server id server-id = 1 #事务安全 sync_master_info = 1 sync_binlog = 1 innodb_support_xa = ON
2.2.Master配置文件全文
[client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 log-bin=mysql-bin binlog_format=mixed server-id = 1 sync_master_info = 1 sync_binlog = 1 innodb_support_xa = ON datadir = /data/mysql/3306/data innodb_data_home_dir = /data/mysql/3306/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /data/mysql/3306/data innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 50 innodb_file_per_table = ON skip_name_resolve = ON [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
2.3.创建复制权限的用户
MariaDB [(none)]> grant replication slave,replication client on *.* to 'repl'@'192.168.1.5' identified by 'slavepass'; Query OK, 0 rows affected (0.39 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.06 sec)
3.配置Slave
3.1.修改Slave配置文件
vim /etc/my.cnf [mysqld] #设置唯一ID server-id = 3 #启用relay log relay_log= relay-log relay_log_index=relay-log.index #事务安全 skip_slave_start = ON sync_relay_log = 1 sync_relay_log_info = 1
3.3.slave配置文件全文
[client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M thread_concurrency = 8 server-id = 3 relay_log= relay-log relay_log_index=relay-log.index skip_slave_start = ON sync_relay_log = 1 sync_relay_log_info = 1 innodb_data_home_dir = /data/mysql/3306/data innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /data/mysql/3306/data innodb_buffer_pool_size = 256M innodb_additional_mem_pool_size = 20M innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_lock_wait_timeout = 50 innodb_file_per_table = ON skip_name_resolve = ON [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
4.开始复制
4.1.在Master上查看binlog Pos点
MariaDB [(none)]> show master status\G *************************** 1. row *************************** File: mysql-bin.000008 Position: 652 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)
4.2.在slave上执行同步操作
MariaDB [(none)]> change master to master_host='192.168.1.4',master_user='repl',master_password='slavepass',master_log_file='mysql-bin.000008',master_log_pos=652; Query OK, 0 rows affected (0.93 sec)
4.3.在slave上启动slave
MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.02 sec)
4.3.在slave上查看slave状态
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.4 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000008 Read_Master_Log_Pos: 652 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 537 Relay_Master_Log_File: mysql-bin.000008 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: 652 Relay_Log_Space: 829 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_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative 1 row in set (0.00 sec)
5.测试同步
5.1.在master创建数据
MariaDB [(none)]> create database ckldb; Query OK, 1 row affected (0.37 sec) MariaDB [(none)]> use ckldb; Database changed MariaDB [ckldb]> create table jone(id int,name varchar(30)); Query OK, 0 rows affected (0.29 sec) MariaDB [ckldb]> insert into jone values(1,'wukaka'); Query OK, 1 row affected (0.49 sec) MariaDB [ckldb]> delete from jone; Query OK, 1 row affected (0.09 sec) MariaDB [ckldb]> insert into jone values(1,'wukaka'),(2,'side'); Query OK, 2 rows affected (0.06 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [ckldb]> select * from jone; +------+--------+ | id | name | +------+--------+ | 1 | wukaka | | 2 | side | +------+--------+ 2 rows in set (0.00 sec)
5.2.在slave上查看
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | ckldb | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.34 sec) MariaDB [(none)]> use ckldb; Database changed MariaDB [ckldb]> show tables; +-----------------+ | Tables_in_ckldb | +-----------------+ | jone | +-----------------+ 1 row in set (0.00 sec) MariaDB [ckldb]> select * from jone; +------+--------+ | id | name | +------+--------+ | 1 | wukaka | | 2 | side | +------+--------+ 2 rows in set (0.00 sec)
注意，如果主库已经运行很久，同步之前最好，备份主库，记录binlog Pos点。将备份导入到slave库
，然后从binlog Pos开始恢复。
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。