要求:1.首先装好系统Redhat AS4.0 ;2.Mysql-max-5.0.16-linux-i686解压缩不需要编译,直接安装就可以: shell> groupadd mysql shell> useradd -g mysql mysql shell> cd /usr/local shell> tar zxvf /data/soft/mysql-max-5.0.16-linux-i686-glibc23.tar.gz shell> ln -s mysql-max-5.0.16-linux-i686-glibc23 mysql shell> cd mysql shell> scripts/mysql_install_db --user=mysql shell> chown -R root . shell> chown -R mysql data shell> chgrp -R mysql . shell> bin/mysqld_safe --user=mysql &具体的操作在其包里的INSTALL-BINARY文件中;3.如果设置了iptables,请将3306端口打开;4.两台服务器的ip:192.168.1.56 192.168.1.57具体过程:1. 建立需要同步的用户和需要同步的数据;登陆到192.168.1.56服务器的数据库上: shell>mysql -uroot -p123456 mysql>CREATE DATABASE bak; //建立数据库 mysql>GRANT REPLICATION SLAVE ON *.* TO tongbu@'192.168.1.57' DENTIFIED BY '123456'; //建立同步的mysql帐号 tongbu,密码为123456,指定只能从192.168.1.57上使用tongbu登陆,并可同步mysql中任意库 mysql>flush privileges;2. 登陆到192.168.1.57服务器的数据库上: shell>mysql -uroot -p123456 mysql>CREATE DATABASE bak; mysql>GRANT REPLICATION SLAVE ON *.* TO tongbu@'192.168.1.56' DENTIFIED BY '123456'; mysql>flush privileges;3. 把mysql/support-files下面的mysql配置文件复制到/etc目录下, # cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf 其中.cnf文档如下: my-small.cnf 内存少于或等于64M,只提供很少的的数据库服务 my-medium.cnf 内存在32M--64M之间而且和其他服务一起使用,例如web my-large.cnf 内存有512M主要提供数据库服务 my-huge.cnf 内存有1G到2G,主要提供数据库服务 4. 修改192.168.1.56上的my.cnf文件,在这里我们把它命名为1号服务器 # vi /etc/my.cnf 在[mysqld]添加一下内容: log-bin=mysql-bin master-host=192.168.157 master-user=tongbu master-password=123456 master-port=3306 binlog-do-db=bak 因为mysql默认是作为主服务器,因此不需要指定 server-id =1 这项5. 修改192.168.1.57上的my.cnf文件,在这里我们把它命名为2号服务器 # vi /etc/my.cnf ,首先在my.cnf文件中找到 server-id这一项,然后修改成: server-id =2 在[mysqld]添加一下内容: master-host=192.168.1.56 master-user=tongbu master-password=123456 master-port =3306 master-connect-retry=30 replicate-do-db=bak log-bin binlog-do-db=bak 解释: master-host=192.168.1.57 表示本机做1号服务器时的master为192.168.1.57; master-user=username 这里表示2号服务器上开放的一个有权限的用户,使其可以从1号机器连接到2号机器并进行复制; master-password=password 表示授权用户的密码; master-port=3306 master上MySQL服务Listen3306端口; master-connect-retry=30 同步间隔时间30秒; replicate-do-db=bak 表示同步bak数据库; log-bin 打开logbin选项以能写到slave的 I/O线程; binlog-do-db=bak 表示别的机器可以同步本机的bak数据库. 6. 为两个数据库中的bak数据库建立或者导入相同的表。7. 重新启动Mysql. # /usr/local/mysql/support-files/mysql.server restart8.这一步非常关键,致是我在这里走了很多冤枉路,不知所措! 登陆到192.168.1.56 数据库: mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.57', MASTER_USER='tongbu', MASTER_PASSWORD='123456' 登陆到192.168.1.57 数据库: mysql>CHANGE MASTER TO MASTER_HOST='192.168.1.56', MASTER_USER='tongbu', MASTER_PASSWORD='123456' 在192.168.1.56查看mysql: mysql> show processlist; +----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+| 1 | system user | | NULL | Connect | 71 | Waiting for master to send event | NULL || 2 | system user | | NULL | Connect | 1150 | Has read all relay log; waiting for the slave I/O thread to update it | NULL || 3 | root | localhost | NULL | Query | 0 | NULL | show processlist || 4 | tongbu | 192.168.1.57:32787 | NULL | Binlog Dump | 16 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+4 rows in set (0.00 sec) 在192.168.1.57查看mysql: mysql> show processlist;+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+| 1 | system user | | NULL | Connect | 37 | Connecting to master | NULL || 2 | system user | | NULL | Connect | 37 | Has read all relay log; waiting for the slave I/O thread to update it | NULL || 3 | tongbu | 192.168.1.56:32829 | NULL | Binlog Dump | 33 | Has sent all binlog to slave; waiting for binlog to be updated | NULL || 4 | root | localhost | NULL | Query | 0 | NULL | show processlist |+----+-------------+--------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+4 rows in set (0.00 sec) 如果看到这样的信息,说明双向同步成功了,否则检查以上步骤!9.现在可以在bak数据库中,插入记录。检查同步情况!
mysql 5.0同步_配置Mysql5.0双机同步
最新推荐文章于 2022-10-12 19:58:08 发布