Centos6.3 is MySQL database master copy backup notes (1/2)

MySQL master-slave copy structure is based on MySQL Bin-log log, from the library through open IO process received the main library Bin-log log increment

Information and save it to the local relay log, and then translate the incremental information obtained from the relay log into SQL by opening the MySQL process

After the sentence is written to from the database.

The master-slave replication structure can actually implement two functions:

1. A database backup instance that serves as the main library from the library

2. Read and write separation, the main library is responsible for normal reading and writing data, from the library only responsible for reading data

The actual production environment, because many applications actually read the database more than the number of times to write the database, so in the early stages of project development, write

When the program makes a judgment, all read operations are pushed to from the library, if the data can not be obtained from the library, then to the main library to obtain data, thereby

In a sense to achieve the separation of read and write, to alleviate the main library IO pressure.

This architecture is recommended for use in production environments.

System environment: centos6.3 x64

Database: mysql-5.6.10

MySQL master:192.168.100.90

MySQL slave:192.168.100.91

MySQL master-slave copy structure is based on MySQL Bin-log log, from the library through open IO process received the main library Bin-log log increment

Information and save it to the local relay log, and then translate the incremental information obtained from the relay log into SQL by opening the MySQL process

After the sentence is written to from the database.

The master-slave replication structure can actually implement two functions:

1. A database backup instance that serves as the main library from the library

2. Read and write separation, the main library is responsible for normal reading and writing data, from the library only responsible for reading data

The actual production environment, because many applications actually read the database more than the number of times to write the database, so in the early stages of project development, write

When the program makes a judgment, all read operations are pushed to from the library, if the data can not be obtained from the library, then to the main library to obtain data, thereby

In a sense to achieve the separation of read and write, to alleviate the main library IO pressure.

This architecture is recommended for use in production environments.

System environment: centos6.3 x64

Database: mysql-5.6.10

MySQL master:192.168.100.90

MySQL slave:192.168.100.91

I. Deployment environment:

1. Close Iptables and SELinux

# Service Iptables Stop

# Setenforce 0

# Vi/etc/sysconfig/selinux

---------------

Selinux=disabled

---------------

2. Installation configuration MySQL Portal: HTTP://WWW.SHOWERLEE.COM/ARCHIVES/6

Two. Master MySQL configuration: (MySQL Master)

To modify the MySQL configuration file:

# VI/ETC/MY.CNF

Add to:

-----------------

# Replication Master Server

# Bin Log Path

Log-bin =/usr/local/mysql/log/bin.log

# Server ID Number

Server-id = 1

# Ignore MySQL system library replication

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

------------

Restart Service

# Service Mysqld Restart

Login MySQL Background:

# mysql-u root-p123456

View login account Now:

> select User ();

Add a sync account number for slave in master:

> Grant replication slave on *.* to ' slave ' @ ' 192.168.100.91 ' identified by ' 123456 ';

To view created users:

> select User.host from Mysql.user;

To view permissions:

> Show grants for ' slave ' @ ' 192.168.100.91 ';

MySQL lock table read only (other account login MySQL can not write table operation, to prevent the backup database after the main MySQL table update, resulting in and

Inconsistent from database content)

> Flush tables with read lock;

To view the lock table countdown time:

> Show variables like '%timeout% ';

------------------------

....

Wait_timeout | 28800

------------------------

All backups of Master's database tables are exported and routed to the slave server.

#/usr/local/mysql/bin/mysqldump-u root-p123456--opt--flush-logs--all-database >

/root/allbak.sql

# CD ~

# SCP Allbak.sql Root@192.168.100.91:/root

View the MySQL offset (the offset value is incremented if the database has a write operation)

# mysql-u root-p123456-e "Show Master Status"

----------------------

+------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+------------+----------+--------------+------------------+-------------------+

|     bin.000009 |              120 |                  |                   | |

+------------+----------+--------------+------------------+-------------------+

----------------------

Ensure that the file and position columns are consistent with the library configuration:

Three. From MySQL configuration (mysql slave)

To modify the MySQL configuration file:

# VI/ETC/MY.CNF

Add to:

--------------------

# Replication Slave Server

# Bin Log path (no need to open Bin-log log)

#log-bin =/usr/local/mysql/log/bin.log

server-id=2

# Read Only:

Read-only

# Ignore MySQL system library replication

Binlog-ignore-db=mysql

Binlog-ignore-db=information_schema

---------------------

Restart Service

# Service Mysqld Restart

Restore server's database to slave

#/usr/local/mysql/bin/mysql-u root-p123456 </root/allbak.sql

Home 1 2 last page