Shulou(Shulou.com)06/01 Report--

I. deployment of the experimental environment

Master server 192.168.18.42 port 3306 = "slave server 192.168.18.44 port 3306

Master server 192.168.18.43 port 3306 = "slave server 192.168.18.44 port 3307

# # Database, the mysql service has been installed, and the installation part is brief. From multiple mysql instances on the server, see another post, "using mysql_multi to realize one machine running more than one mysql"

II. Deploy the server

1. Give slave permissions to the two master servers, and if there are multiple cluster machines, execute them multiple times (our two master libraries use the same account password).

Mysql > grant replication slave on *. * to 'backup'@'192.168.18.44' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

two。 Configure my.cnf on the primary server

Vi / etc/my.cnf

Server-id = 1

Log-bin = mysql-bin # make sure binlog is readable

Read-only = 0 # host, both read and write

# binlog-do-db = test # data needs to be backed up. Multiple writes and multiple lines are required, and all of them are backed up without writing.

Binlog-ignore-db = mysql # databases that do not need to be backed up, multiple writes and multiple lines

Restart the database after editing # service mysqld restart

3. Configure my.cnf from the library server

Vi / etc/my.cnf

[mysqld_multi]

Mysqld = / mysql/bin/mysqld_safe

Mysqladmin = / mysql/bin/mysqladmin

[mysqld1]

Port = 3306

Socket = / tmp/mysql3306.sock

Pid-file = / data/mysql/data1/mysql3306.pid

Datadir = / data/mysql/data1

Skip-name-resolve

Log-bin = mysql-bin-3306

Log_slave_updates

Expire_logs_days = 7

Log-error = / data/mysql/data1/mysql3306.err

Log_slow_queries = mysql3306-slow.log

Long_query_time = 3

Query_cache_size = 64m

Query_cache_limit = 2m

Slave-net-timeout = 10

Server-id = 2 # server id do not repeat with the main library

Master-host = 192.168.18.42 # the ip address of the main library

Master-user = backup # slave account

Master-password = 123456 # password

Master-port = 3306 # main library port

Replicate-ignore-db=mysql # Skip libraries that are not backed up

Master-info-file = master.1842.info

Master-connect-retry = 10

Relay-log = relay-bin-1842 # Relay log

Relay-log-index = relay-bin-1842

Relay-log-info-file = relay-log-1842.info

Default-character-set=gbk

Innodb_data_home_dir = / data/mysql/data1

Innodb_data_file_path = ibdata1:50M:autoextend

Innodb_log_group_home_dir = / data/mysql/data1

Innodb_buffer_pool_size = 3072m

Innodb_file_per_table

Innodb_open_files = 800,

# innodb_flush_method = O_DIRECT

Innodb_flush_method = O_DSYNC

Skip-locking

Key_buffer = 32m

Max_allowed_packet = 16m

Table_cache = 1024

Sort_buffer_size = 8m

Net_buffer_length = 8m

Read_buffer_size = 2m

Read_rnd_buffer_size = 8m

Myisam_sort_buffer_size = 32m

Max_connections = 100

Read_only

Wait_timeout=288000

Interactive_timeout=288000

Log-bin-trust-function-creators=1

Replicate-ignore-db = test

Replicate-ignore-table = mysql.columns_priv

Replicate-ignore-table = mysql.host

Replicate-ignore-table = mysql.db

Replicate-ignore-table = mysql.procs_priv

Replicate-ignore-table = mysql.tables_priv

Replicate-ignore-table = mysql.user

[mysqld2]

Port = 3307

Socket = / tmp/mysql3307.sock

Pid-file = / data/mysql/data2/mysql3307.pid

Datadir = / data/mysql/data2

Skip-name-resolve

Log-bin = mysql-bin-3307

Log_slave_updates

Expire_logs_days = 7

Log-error = / data/mysql/data1/mysql3307.err

Log_slow_queries = mysql3307-slow.log

Long_query_time = 3

Query_cache_size = 64m

Query_cache_limit = 2m

Slave-net-timeout = 10

Server-id = 2 # server id do not repeat with the main library

Master-host = 192.168.18.43 # the ip address of the main library

Master-user = backup # slave account

Master-password = 123456 # password

Master-port = 3306 # main library port

Replicate-ignore-db=mysql # Skip libraries that are not backed up

Master-info-file = master.1843.info

Master-connect-retry = 10

Relay-log = relay-bin-1843

Relay-log-index = relay-bin-1843

Relay-log-info-file = relay-log-1843.info

Default-character-set=gbk

Innodb_data_home_dir = / data/mysql/data2

Innodb_data_file_path = ibdata1:50M:autoextend

Innodb_log_group_home_dir = / data/mysql/data2

Innodb_buffer_pool_size = 3072m

Innodb_file_per_table

Innodb_open_files = 800,

# innodb_flush_method = O_DIRECT

Innodb_flush_method = O_DSYNC

Skip-locking

Key_buffer = 32m

Max_allowed_packet = 16m

Table_cache = 1024

Sort_buffer_size = 8m

Net_buffer_length = 8m

Read_buffer_size = 2m

Read_rnd_buffer_size = 8m

Myisam_sort_buffer_size = 32m

Max_connections = 100

Read_only

Wait_timeout=288000

Interactive_timeout=288000

Log-bin-trust-function-creators=1

Replicate-ignore-db = test

Replicate-ignore-table = mysql.columns_priv

Replicate-ignore-table = mysql.host

Replicate-ignore-table = mysql.db

Replicate-ignore-table = mysql.procs_priv

Replicate-ignore-table = mysql.tables_priv

Replicate-ignore-table = mysql.user

# because it is convenient to capture all the parameters, only a few that are useful to the master and slave are marked

4. Restart the master slave server after the configuration is complete

[root@localhost data1] # mysqld_multi-- config-file=/etc/my.cnf-- user=root-- password=123456 report 1

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

5. Chang master on the slave library

1)。 On date1

Mysql-uroot-p-S / tmp/mysql3306.sock

Mysql > stop slave

Query OK, 0 rows affected (0.00 sec)

Mysql > CHANGE MASTER TO

-> MASTER_HOST='192.168.18.43'

-> MASTER_PORT=3306

-> MASTER_USER='backup'

-> MASTER_PASSWORD='123456'

Query OK, 0 rows affected (0.05 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

2). On date2

Mysql-uroot-p-S / tmp/mysql3307.sock

Mysql > stop slave

Query OK, 0 rows affected (0.00 sec)

Mysql > CHANGE MASTER TO

-> MASTER_HOST='192.168.18.42'

-> MASTER_PORT=3306

-> MASTER_USER='backup'

-> MASTER_PASSWORD='123456'

Query OK, 0 rows affected (0.05 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Third, verify:

1. On the slave server

Show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

two。 Create a database in the main library (on 18.43)

Mysql > create database haifengtest

Query OK, 1 row affected (0.00 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | haifengtest |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

View from the library (on mysql3307.sock)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | haifengtest |

| | mysql |

| | test |

+-+

4 rows in set (0.00 sec)

Fourth, a common problem.

I found the following problem in show slave status\ G; (because I created the library from above)

Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1007

Last_Error: Error 'Can't create database' haifengtest'; database exists' on query. Default database: 'haifengtest'. Query: 'create database haifengtest'

If Replication stops with the above error on Slave, it is generally expected that Slave will ignore the error and continue to synchronize rather than restart Slave.

You can use SQL_SLAVE_SKIP_COUNTER at this time

Mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1

Query OK, 0 rows affected (0.00 sec)

Mysql > start slave

Query OK, 0 rows affected (0.00 sec)

Verify it again.

Show slave status\ G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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.