mysql使用笔记

一. 需求：

1. mysql需要多线程管理，通常是默认端口是主服务器，自定义端口是从服务器，
2. 其中有两个数据库需要双向同步。

二. 配置文件：（my.cnf）

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = admin

#主服务器配置选项[mysqld101]  ##这是mysqld而不是mysql刚开始出现这个错误。然后report无结果。
socket          = /var/lib/mysql/mysql.sock   ##有些PHP服务需要默认的socket值port            = 3306
pid-file        = /var/lib/mysql/mysql.pid
datadir         = /var/lib/mysql
ledir           = /usr/libexec
log             = /var/log/mysqld/mysql101.log
server-id       = 101
log-bin         = /var/lib/mysql/log-bin/mysql-master-101-bin

#从服务器线程配置
[mysqld102]
socket          = /var/lib/mysql/sock/mysql102.sock
port            = 3307
pid-file        = /var/lib/mysql/pid/mysql102.pid
datadir         = /var/lib/mysql
ledir           = /usr/libexec
log             = /var/log/mysqld/mysql102.log
server-id       = 102
log-bin         = /var/lib/mysql/log-bin/mysql-slave-102-binmaster-host=192.168.1.33
master-user=slave_test
master-password=slave_test
master-port=3306

replicate-rewrite-db=sync-test->sync-test
replicate-do-table=sync-table

[mysqld_safe]log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
old_passwords=1

三. 遇到问题及解决方法： 

    在配置mysql的同步的时候遇到了好多问题，最终还是一一的解决了，下面是解决办法，可能不是很好的办法，但是至少是mysql可以正常运行，而且没有报错。

    首先用显示不同的mysql线程及状态
#mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld101 is not running
MySQL server from group: mysqld102 is not running

    然后分别启动线程101和102.但是在启动101的时候就遇到了问题。虽然数据库启动起来了，但是日志里报了错误，
100613 10:09:38  mysqld started
100613 10:09:38  InnoDB: Started; log sequence number 0 43655
100613 10:09:38 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=/var/lib/mysql/mysql-relay-bin' to avoid this problem.100613 10:09:38 [ERROR] Error reading slave log configuration
100613 10:09:38 [ERROR] Failed to initialize the master info structure
100613 10:09:38 [Note] /usr/libexec/mysqld: ready for connections.Version: '5.0.45-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

    刚开始是在配置文件中添加了一行，relay-log=/var/lib/mysql/mysql-relay-bin但是warning没有了，error还是存在，
100613 10:23:21  mysqld started
100613 10:23:21  InnoDB: Started; log sequence number 0 43655
100613 10:23:21 [ERROR] Error reading slave log configuration
100613 10:23:21 [ERROR] Failed to initialize the master info structure
100613 10:23:21 [Note] /usr/libexec/mysqld: ready for connections.Version: '5.0.45-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution 

    后来进入mysql的工作目录查看有好多以mysql-relay-bin.开头的文件，将其全部删除，并将relay-log.info文件删除，结果启动的时候warning及error报错就都没有了。

#rm mysql-relay-bin.* -f
#rm relay-log.info