Mysql master-slave Database Synchronization and Character Set Problems

1. mysql master-slave Database Synchronization problems when using mysql5.0 master-slave Database Synchronization problems: When synchronizing the master-slave database, we may choose which databases require synchronization, while those databases ignore this problem, these two functions depend on etcmy. binlog implemented by binlog_do_db and binlog_ignore_db In the cnf File

1. mysql master-slave Database Synchronization problems encountered when using mysql 5.0 master-slave Database Synchronization problems: During master-slave Database Synchronization, we may choose which databases require synchronization, while those databases ignored, the two functions depend on/etc/my. binlog implemented by binlog_do_db and binlog_ignore_db In the cnf File

1. mysql master-slave Database Synchronization Problems

Some problems occurred when synchronizing data using mysql 5.0 Master/Slave databases:
When synchronizing the master and slave databases, we may choose which databases require synchronization, and those databases ignore these two features rely on/etc/my. the two key names binlog_do_db and binlog_ignore_db In the cnf File

Binlog_do_db = enter the database to be synchronized. Separate multiple databases with commas (,).

Binlog_ignore_db = enter databases that do not need to be synchronized. Separate multiple databases with commas (,).

The primary database reads the key value and can select to generate the database log file. The slave server updates its own database based on the master server log file

Theoretically, these are all feasible. However, some problems may occur in actual implementation. The problems are as follows:

Use mysql-u-p from the terminal to connect to the mysql database. The master server performs database operations. logs are recorded for databases within binlog_do_db, use the api functions provided by mysql to connect to the database and execute SQL statements without writing logs.

After several turnover tests, we finally found that we had removed binlog_ignore_db and commented out the key names together. At this time, the master-slave Database Synchronization was successful, and database operations performed by API functions could be written into logs.

I do not know whether it is an official bug or a database configuration error. This is the only solution so far.

2. mysql Character Set settings

2.1 terminal access:

Generally, you only need to set default-charcter-set = utf8 in/etc/my. cnf to avoid garbled characters during terminal access.

2.2 program connection:

First look at the mysql Character Set

Enter
Show variables like 'Char % ';
The following result is displayed.
+ -------------------------- + ---------------------------- +
| Variable_name | Value |
+ -------------------------- + ---------------------------- +
| Character_set_client | utf8 | client Character Set
| Character_set_connection | utf8 | connection Character Set
| Character_set_database | utf8 | database Character Set
| Character_set_filesystem | binary |
| Character_set_results | utf8 | obtains the result character set.
| Character_set_server | utf8 | Server Character Set
| Character_set_system | utf8 |
| Character_sets_dir |/usr/share/mysql/charsets/|
+ -------------------------- + ---------------------------- +
8 rows in set (0.28 sec)

This is the character set table.

Problem 1: No connection character set is set before UTF-8 data is inserted to a data table with the default Character Set utf8. the character set utf8 is set during query, leading to garbled query.

1. during insertion, according to the default settings of the mysql service, character_set_client, character_set_connection, and character_set_results are all latin1. during insertion, the program regards the utf8 character set as latin1 (this step will not be converted, the data in the stored procedure is converted to the character set conversion process latin1> latin1 => utf8. In this process, each inserted Chinese character will go through the program to the dynamic library; therefore, the characters will be saved from the original 3 bytes (Program utf8) in the last step (latin1 => utf8) to 6 bytes.

2. the query result will go through the utf8 => utf8 Character Set conversion process, and the 6 bytes will be retained and will not be returned, resulting in garbled characters.

Problem 2: An error occurred while retrieving the data.

During insertion, character_set_client, character_set_connection, and character_set_results are both utf8;

The inserted data is converted to the character set utf8 => utf8 => latin1. If the raw data contains/u0000 ~ Unicode characters outside the/u00ff range will be converted to "?" because they cannot be expressed in the latin1 character set. (0x3F) symbol. In future queries, the content cannot be restored regardless of the character set setting.

3. mysql API PHP C

Generally, the PHP program on the page runs for a short period of time. After connecting to the database, use the set names statement to set a connection character. However, when persistent connections are used, be sure to keep the connection smooth and use set names to set the character set again.

Question 1:
Why do we sometimes encounter garbled characters when logging on through a terminal, but when we use a program to perform SQL operations.
Answer: when a program is linked, it uses a dynamic link library to connect to the database, and the program installed by mysql_client on the terminal may have different character sets.
Question 2:
Why does the program run garbled characters after a while, and the data inserted and retrieved are garbled characters.
Answer: There is a time for connection, and the connection will be reconnected upon timeout. There are two ways to set the connection.
3.1 run the SQL statement set names 'utf8' 2
3.2 mysql_options (ledMysql, MYSQL_SET_CHARSET_NAME, "utf8 ");
The first method is only valid for the time being. The second method sets the character set utf8 for each reconnection.

When using the mysql api, initialize the database handle and then use mysql_option to set the MYSQL_SET_CHARASET_NAME attribute to utf8. In this way, you do not need to use the set names statement to specify the connection character set. When mysql_ping is re-disconnected, the connection character is reset to utf8. You do not need to re-connect the set names
Mysql issues character sets. In fact, there is a more absolute way that only the utf8 character set is retained when the database is installed. No matter how the connection is, there will be no garbled characters.

ATTENTION:

The dynamic link library libmysqlclient. so or libmysqlclient. a is called when a program is connected to the database.

The default_character_set setting in my. cnf only affects the character set when the mysql command is used on the terminal to connect to the server. It does not affect the application that uses the libmysqlclient library to connect to mysql.

4. compilation problems

. /Configure -- prefix =/usr/local/mysql -- sysconfdir =/etc -- localstatedir =/var/lib/mysql -- enable-local-infile -- enable-validator -- with-charset = utf8 -- with-big-tables -- with-plugin-partition -- with-plugin-federated

If the version is not compiled, pay attention to the usage during installation; otherwise, the installation may go to the wrong location.

/Usr/local/mysql/bin/mysqld_install -- basedir =/usr/local/mysql -- ldata =/var/lib/mysql

Generally, the database cannot be connected normally. The error message is:

ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql. sock' (2)

In fact, most of the questions on the Internet are the whole question. If you cannot find any link to mysqld. sock, you may wish to see the mysql error log to understand what is going on. Here, the error log is

/Var/lib/mysql /*. err, you will find that mysql cannot be started only because/var/lib/mysql does not allow access to the mysql service. By default, mysql calls the mysql user to start the service, since you know why it cannot be started, it's easy. As long

Chown-R mysql: mysql/var/lib/mysql. If it still cannot be started, you can debug the permission slowly. Generally, it is a permission issue if it cannot be started.

If you still cannot start it, use my complicated permission settings. I do this every time. Generally, there is no problem. See:

Chown-R root/usr/local/mysql
Chgrp-R mysql/usr/local/mysql
Chown-R root/usr/local/mysql/bin
Chgrp-R mysql/usr/local/mysql/bin
Chgrp-R mysql/var/lib/mysql
Chmod 777/var/lib/mysql
Chown-R root/var/lib/mysql
Chgrp-R mysql/var/lib/mysql
Chmod 777/var/lib/mysql
Chown-R root/var/lib/mysql /*
Chgrp-R mysql/var/lib/mysql /*
Chmod 777/var/lib/mysql /*
Chmod 777/usr/local/mysql/lib/mysql/libmysqlclient.