Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to achieve MySQL mysqlbinlog remote backup binary log". In daily operation, I believe many people have doubts about how to achieve MySQL mysqlbinlog remote backup binary log. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to achieve MySQL mysqlbinlog remote backup binary log". Next, please follow the editor to study!

There is no doubt that real-time backup of MySQL database binary logs is a wise strategy. Starting with MySQL5.6, mysqlbinlog supports real-time replication of binlog from a remote server to a local server.

Two test databases, 192.168.8.50 and 192.168.8.63, are now backed up from binary log on 192.168.8.63 to the 192.168.8.50 machine.

1. Log in to 192.168.8.63 database to view binlog

Mysql > show binary logs +-+-+ | Log_name | File_size | +-+-+ | mysql-bin.000001 | 143 | mysql-bin.000002 | 143 | mysql-bin.000003 | 143 | mysql-bin.000004 | 143 | | mysql -bin.000005 | 916 | | mysql-bin.000006 | 120 | +-+-+

2. Make a remote backup at 192.168.8.50

Mysqlbinlog-raw-read-from-remote-server-stop-never-host=192.168.8.63-port=3306-user=root-password=mysql mysql-bin.000001-result-file=/backup/binlog_bakcup/

-- raw:binlog logs are stored on disk in binary format, and if this option is not specified, they are saved as text

-- read-from-remote-server: the binlog used to back up the remote server. If this option is not specified, the local binlog will be found

-- stop-never:mysqlbinlog can only get a few specified binlog from a remote server, or it can save the continuously generated binlog locally. Specify this option, which means that mysqlbinlog will continuously replicate binlog on the remote server as long as the remote server is not shut down or the connection is not disconnected

-- host: the IP address of the database backed up remotely

-- port: the port number of the database backed up remotely

-- user: REPLICATION SLAVE permission is required

-- result-file: backup directory

Mysql-bin.000001: backup from this log:

View the backup:

[mysql@mysql5 binlog_bakcup] $cd / backup/binlog_ bakeries [MySQL @ mysql5 binlog_bakcup] $lltotal 24 mysql mysql RWMI-1 mysql mysql 143 Aug 3 10:26 mysql-bin.000001-rw-r- 1 mysql mysql 143 Aug 3 10:26 mysql-bin.000002-rw-r- 1 mysql mysql 143 Aug 3 10:26 mysql-bin.000003-rw-r- 1 mysql mysql 143 Aug 3 10:26 mysql-bin.000004- Rw-r- 1 mysql mysql 916 Aug 3 10:26 mysql-bin.000005-rw-r- 1 mysql mysql 120 Aug 3 10:26 mysql-bin.000006

3. Form a backup script

#! / bin/shMBL=/usr/local/mysql/bin/mysqlbinlogMYSQLHOST=192.168.8.63MYSQLPORT=3306MYSQLUSER=rootMYSQLPASS=mysqlBACKUPDIR=/backup/binlog_bakcupDATADIR=/mysql/datacd $BACKUPDIRecho "Starting live binlog backup" $MBL-raw-- read-from-remote-server-- stop-never-- host=$MYSQLHOST-- port=$MYSQLPORT-- user=$MYSQLUSER-- password=$MYSQLPASS mysql-bin.000001-- result-file=/backup/binlog_bakcup/ mysql-bin.000001

4. Execute the backup script in the background

Nohup / home/mysql/bin/binlog_backup.sh > / home/mysql/log/binlog_backup.log 2 > & 1 at this point, the study on "how to implement MySQL mysqlbinlog remote backup binary log" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.