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

How to achieve MYSQL master-slave building, in view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

One master, single slave

Steps:

1. Configure the my.cnf of the master host as follows:

[mysqld]

User=mysql

Pid-file=/var/run/mysqld/mysqld.pid

Socket=/var/run/mysqld/mysqld.sock

Port=3306

Basedir=/usr

Datadir=/var/lib/mysql

Tmpdir=/tmp

Log-bin=master-bin

Log-bin-index=master-bin.index

Server-id=1

Restart after configuration to make my.cnf effective

Create a replication user and grant replication slave permissions to the slave node

Mysql > create user repl_user

Mysql > grant replication slave on *. * to repl_user@mt identified by xyzzy

2. Configure the my.cnf of the slave slave as follows:

[mysqld]

User=mysql

Pid-file=/var/run/mysqld/mysqld.pid

Socket=/var/run/mysqld/mysqld.sock

Port=3306

Basedir=/usr

Datadir=/var/lib/mysql

Tmpdir=/tmp

Server-id=2

Relay-log-index=slave-relay-bin.index

Relay-log=slave-relay-bin

Configure the connection between slave and master on slave

Change master to

Master_host='mo'

Master_port='3306'

Master_user'repl_user'

Master_password='xyzzy'

Master_log_file='mysql-bin.00002'

Master_log_pos=101

Start slave

Start slave

3. Check the status of slave

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: mo

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 946

Relay_Log_File: slave-relay-bin.000002

Relay_Log_Pos: 1092

Relay_Master_Log_File: master-bin.000001

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: 946

Relay_Log_Space: 1247

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:

1 row in set (0.00 sec)

If both Slave_IO_Running and Slave_SQL_Running are yes, the configuration is successful. If one of them is no, it is as follows:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Connecting to master

Master_Host: mo

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:

Read_Master_Log_Pos: 4

Relay_Log_File: slave-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File:

Slave_IO_Running: No

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: 0

Relay_Log_Space: 106

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2013

Last_IO_Error: error connecting to master 'repl_user@mo:3306'-retry-time: 60 retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

1 row in set (0.00 sec)

Look at the Last_IO_Errno, Last_IO_Error parameters, and the hostname.err file in the datadir directory to find the error log information, as shown below

[root@mt ~] # cd / var/lib/mysql/

[root@mt mysql] # ls

Ibdata1 ib_logfile0 ib_logfile1 master.info mt.err mysql relay-log.info slave-relay-bin.000001 slave-relay-bin.index test

[root@mt mysql] # cd mt.err

-bash: cd: mt.err: Not a directory

[root@mt mysql] # tail-f n 200 mt.err

Tail: cannot open `n' for reading: No such file or directory

Tail: cannot open `200' for reading: No such file or directory

= = > mt.err

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.