Configure mysql replication error logging

The following applies to the [mysqld] paragraph

Server-id= 1 this line must, 1 can be other numbers, generally recommended is the IP last Rank.

Log-bin=mysql-bin must have this, which is required to turn on binlog binary.

Binlog-do-db=abc optional, only synchronize abc database, there is, separated, you can synchronize multiple databases. When binlog-do-db is miswritten, it will cause mysqld startup to fail, indicating that the pid file cannot be found.

Binlog-ignore-db=abc is optional to synchronize databases other than abc.

When two mysqld are configured on the same machine, when starting the second mysqld, run under the corresponding mysql directory: bin/mysqld_safe & can be started successfully, startup in the bin directory will fail, run in the directory: bin./mysqld_safe-- defaults-file=../my.cnf-- user=mysql & will fail, and the my.cnf configuration file will not be loaded.

Start the second mysqld method: mysql-S / data/mysql2.sock-p123456 # mysql-S path to the second mysql sock, password 123456

Turn off the second mysqld method:

a. First find out the pid of the second mysqld: ps aux | grep 3307 # the second mysqld I configured port is 3307

[root@KAKA ~] # ps aux | grep 3307

Mysql 6136 0.071.2 1011252 352200 pts/0 Sl 13:55 0:03 / soft/mysql/bin/mysqld-basedir=/soft/mysql-- datadir=/data/mysql-- plugin-dir=/soft/mysql/lib/plugin-- user=mysql-- log-error=/data/mysql/KAKA.err-- pid-file=/data/mysql/KAKA.pid-- socket=/tmp/mysql.sock-- port=3307

B. kill 6136 # cannot use kill-9 6136 to avoid database data corruption

8. When initializing the second mysql database, you are prompted that there is no my-default.cnf.

Cp / etc/my.cnf / soft/mysql2/support-files/my-default.cnf

Vi / soft/mysql2/support-files/my-default.cnf modifies the correct path and port of mysql2

9. Set up slave

a. Modify the my.cnf file, delete the line server-id=, or change it to something else, which cannot be the same as master.

b. Depending on the configuration of master, add lines:

Replicate-do-db=abc # synchronize database abc

# replicate-ignore-db=abc # synchronize databases other than abc

10. The problem that mysqld cannot be started after manually deleting the file of mysql-bin.0000* under data:

A, comment out the log-bin=mysql-bin and binlog-do-db=abc lines in the my.cnf file

B, delete the mysql-bin.index file in the data directory.

11. Show slave status\ G; Slave_IO_Running: connecting's problem

Please confirm that the user with replication is set up on master mysql.

twelve。 After restarting master mysql, show slave status\ G; Slave_IO_Running: the problem of becoming connecting

Since mysql-bin.000001 is no longer the latest log after restart, and it is 0002 at this time, you need to restart the slave synchronization function in slave mysql: stop slave; start slave

13. After setting flush tables with read lock; for slave mysql, even if the master mysql is started, slave mysql will not respond. Synchronization continues when unlock tables; is executed.