mysql主从配置（修正版）

 

1,安装apache 和 mysql(mysql版本相同)

 

--------------------------------------------------------------------------------

安装mysql

cd /usr/local/src
tar zxvf mysql-max-4.0.27-pc-linux-gnu-i686-glibc23.tar.gz
mv mysql-max-4.0.27-pc-linux-gnu-i686-glibc23 /usr/local/mysql
groupadd mysql ; useradd -g mysql mysql
cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf
cd /usr/local/mysql
scripts/mysql_install_db --user=mysql
chown -R root .
chown -R mysql data
chgrp -R mysql .
/usr/local/mysql/bin/mysqld_safe --user=mysql &

修改mysql数据库超级用户root的缺省密码:
/usr/local/mysql/bin/mysqladmin -u root password 'mysql'

关闭mysql服务器
cd /usr/local/mysql/bin
./mysqladmin -u root -p    shutdown

设定开机就启动mysql,进入源码目录下

# cd /usr/local/mysql
# cp support-files/mysql.server /etc/init.d/mysql

# chmod +x /etc/init.d/mysql
# chkconfig --level 345 mysql on
# service mysql restart（可能不会显示如下格式，用ps -aux可以查看是否起来）
Shutting down MySQL.                                          [  确定  ]
Starting MySQL                                                [  确定  ]
 
--------------------------------------------------------------------------------

 

2,设置

如：主机(10.1.134.144)

Vi etc/my.cnf （添加到[mysqld]下面）

server-id       = 1

log-error = /usr/local/mysql/logs/mysql.err

log-bin = /usr/local/mysql/logs/10_1_1_1

binlog-do-db = mysql(需同步的数据库名)

binlog-do-db = test

Mkdir logs

Chmod 777 logs

注意:在my.cnf下有个默认的"server-id = 1"和"log-bin"必需"禁掉"

/usr/local/mysql/bin/mysql -uroot -p

GRANT ALL PRIVILEGES ON *.* TO (从) IDENTIFIED BY '密码（数据库密码）' WITH GRANT OPTION;  //授予从机在主机数据库权限

Show master status;(查看指针是否一致)

-----------------------------------------------------------------------------------
 

如：从机:(10.1.134.149)

Vi etv/my.cnf （添加到[mysqld]下面）

server-id       =  4(和主机错开)

master-host     =  10.1.134.144 //主机IP

master-user     =  root

master-password =  123456

master-port     =  3306

relay-log= /usr/local/mysql/logs/mysql_4_3306

replicate-do-db = mysql(必需和主机保持一致)

replicate-do-db = test(必需和主机保持一致)

Mkdir logs

Chmod 777 logs

注意:在my.cnf下有个默认的"server-id = 1"和"log-bin"必需"禁掉"

/usr/local/mysql/bin/mysql -uroot -p

GRANT ALL PRIVILEGES ON *.* TO (主) IDENTIFIED BY '密码（数据库密码）' WITH GRANT OPTION;  //授予主机在从机数据库权限

CHANGE MASTER TO MASTER_HOST='主IP',
MASTER_USER='root',
MASTER_PASSWORD='mysql';

Show slave status\G;(查看指针是否一致，如果命令不行，就把;去掉试试)

 

-----------------------------------------------------------------------------------

 

测试方法:

可先查看主,从机的指计是否一致.(主机:show master status;    从机:show slave status\G;)

用phpmyadmin登录主机,删掉其中某一个数据库里的字段(也可以增加),在到从机上看,是否有同步.(正常下主机有任何的操作,从机上都会有同步显示)

 

-----------------------------------------------------------------------------------