Mysqlreplication-based problem summary _ MySQL

Summary of mysqlreplication-based problems bitsCN.com

After getting started with replication, I began to think about how to configure and try it. although there were a lot of problems: I had to check the information on the Internet and finally solved some problems. now I will share my experiences:
The first problem is to configure my. cnf for the master.

Server-id = 1
Log_bin = mysql-bin
# Log_bin =/var/log/mysql/mysql-bin.log
Expire_logs_days = 10
Max_binlog_size = 100 M
# Binlog_do_db = include_database_name
# Binlog_ignore_db = include_database_name

The configuration here is written in the installation book, and I am not aware of the logbin problem. because my mysql is automatically installed with apt-get, I do not know where some mysql files are, this led to the later generation of the mysql-bin000001 log file do not know where to put, tangled for a long time, and finally use the most of the method to find (when the computer instantly hot ), found in the/var/lib/mysql/directory.

After restarting mysql, check the status:

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000001 | 106 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

It proves that the log has been started. But the second time I found out:

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000002 | 106 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

It turned out that when the mysql service restarts once, it will automatically re-open a day file, all the file directories are in the mysql-bin.index.

Then, I configured my slave and used a windows PC for testing because of insufficient resources. However, there are many configuration files in the mysql directory, I cannot tell which configuration is used. set server-id to 2 in ini. Restart the mysql service and run the mysqladmin command to stop and start mysql.Then run the following command:

Mysql> CHANGE MASTER

-> MASTER_HOST = '10. 5.110.234 ',

-> MASTER_USER = 'Replica ',

-> MASTER_PASSWORD = 'Replica ',

-> MASTER_LOG_FILE = 'MySQL-bin.000001 ',

-> MASTER_LOG_POS = 228;

Query OK, 0 rows affected (0.01 sec)

Restart slave
The current status is queried after startup, which makes me confused. my io-thread is not enabled. Start slave io-thread. if it runs successfully, I/O cannot be opened.

You can ping the server. Then I remotely log on to mysql and find out the cause. mysql itself is not connected to the master server:

ERROR 2003 (HY000): Can't connect to MySQL server on '10. 5.110.234 '(111)

After checking on the Internet for a long time, I finally got excited by a solution in the my. cnf configuration file of mysql on my server.

• Bind-address = 127.0.0.1
Just remove it. Reconnect again.

• This problem has just been fixed and a new problem has emerged. I inserted a piece of data on the master server, and the result shows the status in slave.

Last_Error: Error 'table' test. chen 'doesn' t exist 'on query. default database: 'test '. query: 'Insert into chen values (112, 'Chen', 'Chen ')'

The problem here is very simple because the database on the server does not have this table, so you can view the table show create in the master database and then create a table on the slave database. Done ......

1 row in set (0.02 sec)

Mysql> insert into chen values (1111, 'Chen', 'Chen ');
Query OK, 1 row affected (0.00 sec)

View from the database:


Mysql> select * from chen;
+ ------ + --------- +
| Id | name | address |
+ ------ + --------- +
| 112 | chen |
| 1111 | chen |
+ ------ + --------- +
2 rows in set (0.01 sec)

BitsCN.com