\u4e00\u822c\u5728\u7ebf\u589e\u52a0\u4ece\u5e93\u6709\u4e24\u79cd\u65b9\u5f0f\uff0c\u4e00\u79cd\u662f\u901a\u8fc7mysqldump\u5907\u4efd\u4e3b\u5e93\uff0c\u6062\u590d\u5230\u4ece\u5e93\uff0cmysqldump\u662f\u903b\u8f91\u5907\u4efd\uff0c\u6570\u636e\u91cf\u5927\u65f6\uff0c\u5907\u4efd\u901f\u5ea6\u4f1a\u5f88\u6162\uff0c\u9501\u8868\u7684\u65f6\u95f4\u4e5f\u4f1a\u5f88\u957f\u3002\u53e6\u4e00\u79cd\u662f\u901a\u8fc7xtrabackup\u5de5\u5177\u5907\u4efd\u4e3b\u5e93\uff0c\u6062\u590d\u5230\u4ece\u5e93\uff0cxtrabackup\u662f\u7269\u7406\u5907\u4efd\uff0c\u5907\u4efd\u901f\u5ea6\u5feb\uff0c\u4e0d\u9501\u8868\u3002\u4e3a\u4ec0\u4e48\u4e0d\u9501\u8868\uff1f\u56e0\u4e3a\u81ea\u8eab\u4f1a\u76d1\u63a7\u4e3b\u5e93\u65e5\u5fd7\uff0c\u5982\u679c\u6709\u66f4\u65b0\u7684\u6570\u636e\uff0c\u5c31\u4f1a\u5148\u5199\u5230\u4e00\u4e2a\u6587\u4ef6\u4e2d\uff0c\u7136\u540e\u518d\u56de\u5f52\u5230\u5907\u4efd\u6587\u4ef6\u4e2d\uff0c\u4ece\u800c\u4fdd\u6301\u6570\u636e\u4e00\u81f4\u6027\u3002<\/p>\n\n\n\n
\u670d\u52a1\u5668\u4fe1\u606f\uff1a
<\/p>\n\n\n\n
\u4e00\u3001mysqldump\u65b9\u5f0f
MySQL\u4e3b\u4ece\u662f\u57fa\u4e8ebinlog\u65e5\u5fd7\uff0c\u6240\u4ee5\u5728\u5b89\u88c5\u597d\u6570\u636e\u5e93\u540e\u5c31\u8981\u5f00\u542fbinlog\u3002\u8fd9\u6837\u597d\u5904\u662f\uff0c\u4e00\u65b9\u9762\u53ef\u4ee5\u7528binlog\u6062\u590d\u6570\u636e\u5e93\uff0c\u53e6\u4e00\u65b9\u9762\u53ef\u4ee5\u4e3a\u4e3b\u4ece\u505a\u51c6\u5907\u3002
\u539f\u6709\u4e3b\u5e93\u914d\u7f6e\u53c2\u6570\u5982\u4e0b\uff1a<\/p>\n\n\n\n
vi my.cnf
server-id = 1 #id\u8981\u552f\u4e00
log-bin = mysql-bin #\u5f00\u542fbinlog\u65e5\u5fd7
auto-increment-increment = 1 #\u5728Ubuntu\u7cfb\u7edf\u4e2dMySQL5.5\u4ee5\u540e\u5df2\u7ecf\u9ed8\u8ba4\u662f1
auto-increment-offset = 1
slave-skip-errors = all #\u8df3\u8fc7\u4e3b\u4ece\u590d\u5236\u51fa\u73b0\u7684\u9519\u8bef<\/p><\/blockquote>\n\n\n\n1. \u4e3b\u5e93\u521b\u5efa\u540c\u6b65\u8d26\u53f7<\/p>\n\n\n\n
mysql> grant all on .<\/em> to ‘sync’@’192.168.18.%’ identified by ‘sync’;<\/p><\/blockquote>\n\n\n\n
2. \u4ece\u5e93\u914d\u7f6eMySQL<\/p>\n\n\n\n
vi my.cnf
server-id = 3 #\u8fd9\u4e2a\u8bbe\u7f6e3
log-bin = mysql-bin #\u5f00\u542fbinlog\u65e5\u5fd7
auto-increment-increment = 1 #\u8fd9\u4e24\u4e2a\u53c2\u6570\u5728Ubuntu\u7cfb\u7edf\u4e2dMySQL5.5\u4ee5\u540e\u90fd\u5df2\u7ecf\u9ed8\u8ba4\u662f1
auto-increment-offset = 1
slave-skip-errors = all #\u8df3\u8fc7\u4e3b\u4ece\u590d\u5236\u51fa\u73b0\u7684\u9519\u8bef<\/p><\/blockquote>\n\n\n\n3. \u5907\u4efd\u4e3b\u5e93<\/p>\n\n\n\n
mysqldump -uroot -p123 –routines –single_transaction –master-data=2 –databases weibo > weibo.sql<\/p><\/blockquote>\n\n\n\n
\u53c2\u6570\u8bf4\u660e\uff1a
<\/p>\n\n\n\n
- \u2013routines\uff1a\u5bfc\u51fa\u5b58\u50a8\u8fc7\u7a0b\u548c\u51fd\u6570<\/li>
- \u2013single_transaction\uff1a\u5bfc\u51fa\u5f00\u59cb\u65f6\u8bbe\u7f6e\u4e8b\u52a1\u9694\u79bb\u72b6\u6001\uff0c\u5e76\u4f7f\u7528\u4e00\u81f4\u6027\u5feb\u7167\u5f00\u59cb\u4e8b\u52a1\uff0c\u7136\u540eunlock tables;\u800clock-tables\u662f\u9501\u4f4f\u4e00\u5f20\u8868\u4e0d\u80fd\u5199\u64cd\u4f5c\uff0c\u76f4\u5230dump\u5b8c\u6bd5\u3002<\/li>
- \u2013master-data\uff1a\u9ed8\u8ba4\u7b49\u4e8e1\uff0c\u5c06dump\u8d77\u59cb\uff08change master to\uff09binlog\u70b9\u548cpos\u503c\u5199\u5230\u7ed3\u679c\u4e2d\uff0c\u7b49\u4e8e2\u662f\u5c06change master to\u5199\u5230\u7ed3\u679c\u4e2d\u5e76\u6ce8\u91ca\u3002<\/li><\/ul>\n\n\n\n
4. \u628a\u5907\u4efd\u5e93\u62f7\u8d1d\u5230\u4ece\u5e93<\/p>\n\n\n\n
scp weibo.sql root@192.168.18.214:\/home\/root<\/p><\/blockquote>\n\n\n\n
5. \u5728\u4e3b\u5e93\u521b\u5efatest_tb\u8868\uff0c\u6a21\u62df\u6570\u636e\u5e93\u65b0\u589e\u6570\u636e\uff0cweibo.sql\u662f\u6ca1\u6709\u7684<\/p>\n\n\n\n
mysql>\u00a0create\u00a0table\u00a0test_tb(id\u00a0int,name\u00a0varchar(30));<\/p><\/blockquote>\n\n\n\n
6. \u4ece\u5e93\u5bfc\u5165\u5907\u4efd\u5e93<\/p>\n\n\n\n
mysql -uroot -p123 -e ‘create database weibo;’
mysql -uroot -p123 weibo < weibo.sql<\/p><\/blockquote>\n\n\n\n7. \u5728\u5907\u4efd\u6587\u4ef6weibo.sql\u67e5\u770bbinlog\u548cpos\u503c<\/p>\n\n\n\n
head -25 weibo.sql
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=105; #\u5927\u698222\u884c<\/p><\/blockquote>\n\n\n\n8. \u4ece\u5e93\u8bbe\u7f6e\u4ece\u8fd9\u4e2a\u65e5\u5fd7\u70b9\u540c\u6b65\uff0c\u5e76\u542f\u52a8<\/p>\n\n\n\n
mysql> change master to master_host=’192.168.18.212′,
-> master_user=’sync’,
-> master_password=’sync’,
-> master_log_file=’mysql-bin.000001′,
-> master_log_pos=105;
mysql> start slave;<\/p><\/blockquote>\n\n\n\nmysql> show slave status\\G;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect\u2026
Connection id: 90
Current database: *** NONE ***
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.212
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 358
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 504
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
\u2026\u2026<\/p><\/blockquote>\n\n\n\n\u53ef\u4ee5\u770b\u5230IO\u548cSQL\u7ebf\u7a0b\u5747\u4e3aYES\uff0c\u8bf4\u660e\u4e3b\u4ece\u914d\u7f6e\u6210\u529f\u3002
<\/p>\n\n\n\n9. \u4ece\u5e93\u67e5\u770bweibo\u5e93\u91cc\u9762\u7684\u8868<\/p>\n\n\n\n
<\/p>\n\n\n\n
mysql>\u00a0show\u00a0tables; +—————————+ <\/p>
|\u00a0Tables_in_weibo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0<\/p>
| +—————————+ <\/p>
|\u00a0test_tb\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/p><\/blockquote>\n\n\n\n
\u53d1\u73b0\u521a\u624d\u6a21\u62df\u521b\u5efa\u7684test_tb\u8868\u5df2\u7ecf\u540c\u6b65\u8fc7\u6765\uff01
<\/p>\n\n\n\n\u4e8c\u3001xtrabackup\u65b9\u5f0f\uff08\u63a8\u8350\uff09<\/p>\n\n\n\n
\u5728\u4e0a\u9762\u914d\u7f6e\u57fa\u7840\u4e0a\u505a\u5b9e\u9a8c\uff0c\u5148\u5220\u9664\u6389\u4ece\u5e93\u914d\u7f6e\uff1a<\/p>\n\n\n\n
mysql> stop slave; #\u505c\u6b62\u540c\u6b65
mysql> reset slave; #\u6e05\u9664\u4ece\u8fde\u63a5\u4fe1\u606f
mysql> show slave status\\G; #\u518d\u67e5\u770b\u4ece\u72b6\u6001\uff0c\u53ef\u4ee5\u770b\u5230IO\u548cSQL\u7ebf\u7a0b\u90fd\u4e3aNO
mysql> drop database weibo; #\u5220\u9664weibo\u5e93<\/p><\/blockquote>\n\n\n\n\u6b64\u65f6\uff0c\u4ece\u5e93\u73b0\u5728\u548c\u65b0\u88c5\u7684\u4e00\u6837\uff0c\u7ee7\u7eed\u524d\u8fdb\uff01
<\/p>\n\n\n\n<\/p>\n\n\n\n
1. \u4e3b\u5e93\u4f7f\u7528xtrabackup\u5907\u4efd<\/p>\n\n\n\n
innobackupex –user=root –password=123 .\/<\/p><\/blockquote>\n\n\n\n
\u751f\u6210\u4e00\u4e2a\u4ee5\u65f6\u95f4\u4e3a\u547d\u540d\u7684\u5907\u4efd\u76ee\u5f55\uff1a2019-02-15_21-05-43<\/p>\n\n\n\n
ll 2019-02-15_21-05-43\/
total 18480
drwxr-xr-x 5 root root 4096 Jul 1 16:49 .\/
drwx—— 4 root root 4096 Jul 1 16:49 ..\/
-rw-r–r– 1 root root 188 Jul 1 16:49 backup-my.cnf
-rw-r—– 1 root root 18874368 Jul 1 16:49 ibdata1
drwxr-xr-x 2 root root 4096 Jul 1 16:49 mysql\/
drwxr-xr-x 2 root root 4096 Jul 1 16:49 performance_schema\/
drwxr-xr-x 2 root root 12288 Jul 1 16:49 weibo\/
-rw-r–r– 1 root root 21 Jul 1 16:49 xtrabackup_binlog_info
-rw-r—– 1 root root 89 Jul 1 16:49 xtrabackup_checkpoints
-rw-r–r– 1 root root 563 Jul 1 16:49 xtrabackup_info
-rw-r—– 1 root root 2560 Jul 1 16:49 xtrabackup_logfile<\/p><\/blockquote>\n\n\n\n2. \u628a\u5907\u4efd\u76ee\u5f55\u62f7\u8d1d\u5230\u4ece\u5e93\u4e0a<\/p>\n\n\n\n
scp -r 2019-02-15_20-49-43 root@192.168.18.214:\/home\/root<\/p><\/blockquote>\n\n\n\n
3. \u4ece\u5e93\u4e0a\u628aMySQL\u670d\u52a1\u505c\u6389\uff0c\u5220\u9664datadir\u76ee\u5f55\uff0c\u5c06\u5907\u4efd\u76ee\u5f55\u91cd\u547d\u540d\u4e3adatadir\u76ee\u5f55<\/p>\n\n\n\n
sudo rm -rf \/var\/lib\/mysql\/
sudo mv 2019-02-15_21-05-43\/ \/var\/lib\/mysql
sudo chown mysql.mysql -R \/var\/lib\/mysql
sudo \/etc\/init.d\/mysql start
ps -ef |grep mysql #\u67e5\u770b\u5df2\u7ecf\u6b63\u5e38\u542f\u52a8
mysql 8832 1 0 16:55 ? 00:00:00 \/usr\/sbin\/mysqld<\/p><\/blockquote>\n\n\n\n4. \u5728\u4e3b\u5e93\u521b\u5efatest_tb2\u8868\uff0c\u6a21\u62df\u6570\u636e\u5e93\u65b0\u589e\u6570\u636e<\/p>\n\n\n\n
mysql> create table test_tb2(id int,name varchar(30));<\/p><\/blockquote>\n\n\n\n
5. \u4ece\u5907\u4efd\u76ee\u5f55\u4e2dxtrabackup_info\u6587\u4ef6\u83b7\u53d6\u5230binlog\u548cpos\u4f4d\u7f6e<\/p>\n\n\n\n
cat \/var\/lib\/mysql\/xtrabackup_info
uuid = 201af9db-1fce-11e5-96b0-525400e4239d
name =
tool_name = innobackupex
tool_command = –user=root –password=\u2026 .\/
tool_version = 1.5.1-xtrabackup
ibbackup_version = xtrabackup version 2.2.11 based on MySQL server 5.6.24 Linux (x86_64) (revision id: )
server_version = 5.5.43-0ubuntu0.12.04.1-log
start_time = 2019-02-15 21:05:43
end_time = 2019-02-15 21:05:46
lock_time = 1
binlog_pos = filename ‘mysql-bin.000001’, position 429 #\u8fd9\u4e2a\u4f4d\u7f6e
innodb_from_lsn = 0
innodb_to_lsn = 1598188
partial = N
incremental = N
format = file
compact = N
compressed = N<\/p><\/blockquote>\n\n\n\n6. \u4ece\u5e93\u8bbe\u7f6e\u4ece\u8fd9\u4e2a\u65e5\u5fd7\u70b9\u540c\u6b65\uff0c\u5e76\u542f\u52a8<\/p>\n\n\n\n
mysql> change master to master_host=’192.168.18.212′,
-> master_user=’sync’,
-> master_password=’sync’,
-> master_log_file=’mysql-bin.000001′,
-> master_log_pos=429;
mysql> start slave;<\/p><\/blockquote>\n\n\n\nmysql> show slave status\\G;
* 1. row *
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.18.212
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 539
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 363
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
\u2026\u2026<\/p><\/blockquote>\n\n\n\n\u53ef\u4ee5\u770b\u5230IO\u548cSQL\u7ebf\u7a0b\u5747\u4e3aYES\uff0c\u8bf4\u660e\u4e3b\u4ece\u914d\u7f6e\u6210\u529f\u3002<\/p>\n\n\n\n
9. \u4ece\u5e93\u67e5\u770bweibo\u5e93\u91cc\u9762\u7684\u8868<\/p>\n\n\n\n
mysql> show tables;
+—————————+
| Tables_in_weibo |
+—————————+
| test_tb |<\/p><\/blockquote>\n\n\n\n\u53d1\u73b0\u521a\u624d\u6a21\u62df\u521b\u5efa\u7684test_tb2\u8868\u5df2\u7ecf\u540c\u6b65\u8fc7\u6765\u3002
<\/p>\n","protected":false},"excerpt":{"rendered":"\u4e00\u822c\u5728\u7ebf\u589e\u52a0\u4ece\u5e93\u6709\u4e24\u79cd\u65b9\u5f0f\uff0c\u4e00\u79cd\u662f\u901a\u8fc7mysqldump\u5907\u4efd\u4e3b\u5e93\uff0c\u6062\u590d\u5230\u4ece\u5e93\uff0cm … \u7ee7\u7eed\u9605\u8bfb