MySQL AB复制及簇群

MySQL AB复制及簇群

名称	角色	IP地址
mysql-master （RHEL5）	主	Eth0:192.168.1.1
mysql-slave (RHEL5)	从	Eth0:192.168.1.2

MySQL实验一：（MySQL A/B复制试验） 此实验所用版本（RHEL5.5）制作 1.基础安装mysql. 首先干净的系统，不是干净的系统不要紧，反正自己搞明白就行了。 在Master上安装mysql. [root@localhost ~]# mount /dev/cdrom /media/ mount: block device /dev/cdrom is write-protected, mounting read-only [root@localhost ~]# vim /etc/yum.repos.d/rhel-debuginfo.repo [rhel-debuginfo] name=Red Hat Enterprise Linux $releasever - $basearch – Debug baseurl=file:///media/Server enabled=1 gpgcheck=0 [root@localhost ~]# yum -y install mysql* [root@localhost ~]# chkconfig --add mysqld [root@localhost ~]# chkconfig mysqld on [root@localhost ~]# service mysqld start [root@localhost ~]# mysqladmin -uroot password “123” //设置root密码 Slave上同样方式安装mysql，并做如上操作 2.Master上mysql的设置。 在这里我就用test数据库来做测试，在test数据库里新建一个data表，并添加一些数据，具体操作如下： [root@localhost ~]# mysql -uroot -p123 mysql> use test; Database changed mysql> create table data(name varchar(20),address varchar(50),phone varchar(20)); Query OK, 0 rows affected (0.04 sec)   mysql> insert into data(name,address,phone)values('zhangsan','beijing','123123'); Query OK, 1 row affected (0.00 sec)   mysql> select * from data; +----------+---------+--------+ | name     | address | phone | +----------+---------+--------+ | zhangsan | beijing | 123123 | +----------+---------+--------+ 1 row in set (0.00 sec) 设置数据库同步帐户： mysql> grant replication slave,replication client,reload,super on *.* to backup@192.168.1.2 identified by '123'; Query OK, 0 rows affected (0.00 sec) (授与从192.168.1.2主机上登录用户backup数据复制权限，4.02版本以前用：GRANT FILE ON *.* TO backup@192.168.1.2 IDENTIFIED BY ‘123’;) mysql> flush privileges; //使权限立即生效 Query OK, 0 rows affected (0.01 sec) mysql> use mysql; //切换到mysql数据库 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Database changed mysql> select user,host from user;   //查看新建用户 +--------+-----------------------+ | user   | host                  | +--------+-----------------------+ | root   | 127.0.0.1             | | backup | 192.168.1.2           | |        | localhost             | | root   | localhost             | |        | localhost.localdomain | | root   | localhost.localdomain | +--------+-----------------------+ 6 rows in set (0.00 sec) mysql> exit Bye 修改mysql主配置文件/etc/my.conf 在[mysqld]中加入以下内容 [root@localhost ~]# service mysqld stop  //先停止MYSQL ,修改完毕后启用 停止 MySQL：                                               [确定] [root@localhost ~]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql 添加如下行：当自己作为服务端同步时，与客户端之间同步的设置信息 server-id=1    //设置服务器的ID号 log-bin       //设置同步log binlog-do-db=test     //设置同步数据库 max_binlog_size=104857600    //设置同步log最大size:104857600字节 replicate-same-server-id      //在复制过程中同步相同的master id号 当自己作为客户端同步时，与主机之间同步的设置信息 master-host=192.168.1.2    //主机IP master-user=backup       //登陆服务端的账户名 master-password=123  //登陆服务端的账户密码 master-port=3306                  //服务端打开的端口 master-connect-retry=60       //与服务端断点重试间隔为60秒 replicate-do-db=test          //表示同步test数据库 binlog-ignore-db=mysql           //设置不同步的数据库 [root@localhost ~]# service mysqld restart  //启用Mysql 停止 MySQL：                                               [失败] 启动 MySQL：                                               [确定] 备份test数据库 [root@localhost ~]# mysqldump -uroot -p123 test > /opt/test.sql [root@localhost ~]# scp /opt/test.sql root@192.168.1.2: ./ [root@localhost ~]# scp /etc/my.cnf root@192.168.1.2: ./   //将mysql配置文件拷贝到Slave上，修改 scp test.sql root@192.168.1.2: ./（将test.sql复制到Slave的/root目录下） 至此Master服务器上有关mysql的设置已完成，下一步开始配置Slave 设置Slave 设置数据库同步帐户： 设置Slave 设置数据库同步帐户： [root@localhost ~]# mysql -uroot -p123 test < test.sql //倒入test数据库中内容 [root@localhost ~]# mysql -uroot -p123 mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | data           | +----------------+ 1 row in set (0.00 sec) 设置数据库同步帐户： mysql> grant replication slave,replication client,reload,super on *.* to backup@192.168.1.1 identified by '123'; Query OK, 0 rows affected (0.01 sec) (授与从192.168.1.2主机上登录用户backup数据复制权限，4.02版本以前用：GRANT FILE ON *.* TO backup@192.168.1.2 IDENTIFIED BY ‘123’;) mysql> flush privileges; //使权限立即生效 Query OK, 0 rows affected (0.01 sec) mysql> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A   Database changed mysql> select user,host from user; +--------+-----------------------+ | user   | host                  | +--------+-----------------------+ | root   | 127.0.0.1             | | backup | 192.168.1.1           | |        | localhost             | | root   | localhost             | |        | localhost.localdomain | | root   | localhost.localdomain | +--------+-----------------------+ 6 rows in set (0.00 sec) mysql> exit Bye 修改mysql主配置文件/etc/my.conf 在[mysqld]中加入以下内容 [root@localhost ~]# service mysqld stop  //先停止MYSQL ,修改完毕后启用 停止 MySQL：                                               [确定] [root@localhost ~]# cp my.cnf /etc/my.cnf   //将从Master上拷贝到/root下的mysql配置文件拷贝到/etc下，修改文件中蓝色部分 cp：是否覆盖“/etc/my.cnf”? y [root@localhost ~]# vi /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql 添加如下行：当自己作为服务端同步时，与客户端之间同步的设置信息 server-id=2   //设置服务器的ID号 log-bin       //设置同步log binlog-do-db=test     //设置同步数据库 max_binlog_size=104857600    //设置同步log最大size:104857600字节 replicate-same-server-id      //在复制过程中同步相同的master id号 当自己作为客户端同步时，与主机之间同步的设置信息 master-host=192.168.1.2   //主机IP master-user=backup       //登陆服务端的账户名 master-password=123  //登陆服务端的账户密码 master-port=3306                  //服务端打开的端口 master-connect-retry=60       //与服务端断点重试间隔为60秒 replicate-do-db=test          //表示同步test数据库 binlog-ignore-db=mysql           //设置不同步的数据库 [root@localhost ~]# service mysqld restart  //启用Mysql 停止 MySQL：                                               [失败] 启动 MySQL：                                               [确定] 在Master和Slave上分别重启mysql服务 [root@localhost ~]# service mysqld restart 停止 MySQL：                                               [确定] 启动 MySQL：                                               [确定] You have mail in /var/spool/mail/root 进入数据库，分别查询配置 [root@localhost ~]# mysql -uroot -p123 Mysql>show master status; mysql>show slve status\G; mysql>show processlist G; Show Slave status:此处Slave_IO_Running ,Slave_SQL_Running 都应该是yes,表示从库的I/O,Slave_SQL线程都正确开启. 在Mysql中可通过以下命令来查看主从状态 show master status 查看master状态 show slave status 查看slave状态 show processlist G 查看当前进程 stop slave 暂时停止slave进程 start slave 开始slave进程 在Master服务器上MySQL命令符下输入： mysql> show master status; +-------------------+----------+--------------+------------------+ | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | mysqld-bin.000004 |       98 | test         | mysql           | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> show slave status\G; *************************** 1. row ***************************              Slave_IO_State: Connecting to master                 Master_Host: 192.168.1.2                 Master_User: backup                 Master_Port: 3306               Connect_Retry: 60             Master_Log_File:         Read_Master_Log_Pos: 4              Relay_Log_File: mysqld-relay-bin.000004               Relay_Log_Pos: 98       Relay_Master_Log_File:            Slave_IO_Running: Yes           Slave_SQL_Running: Yes             Replicate_Do_DB: test         Replicate_Ignore_DB:          Replicate_Do_Table:      Replicate_Ignore_Table:     Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table:                  Last_Errno: 0                  Last_Error:                Skip_Counter: 0         Exec_Master_Log_Pos: 0             Relay_Log_Space: 98             Until_Condition: None              Until_Log_File:               Until_Log_Pos: 0          Master_SSL_Allowed: No          Master_SSL_CA_File:          Master_SSL_CA_Path:             Master_SSL_Cert:           Master_SSL_Cipher:              Master_SSL_Key:       Seconds_Behind_Master: NULL 1 row in set (0.00 sec)   ERROR: No query specified 表示正常！ 到此mysql的双机互备已基本完成，在两台服务器的mysql数据库中任意添加数据，都可以同步到对端服 mysql> show processlist\G; *************************** 1. row ***************************      Id: 2    User: system user    Host:      db: NULL Command: Connect    Time: 422  State: Connecting to master    Info: NULL *************************** 2. row ***************************      Id: 3    User: system user    Host:      db: NULL Command: Connect    Time: 422  State: Has read all relay log; waiting for the slave I/O thread to update it    Info: NULL *************************** 3. row ***************************      Id: 6    User: root    Host: localhost      db: NULL Command: Query    Time: 0  State: NULL    Info: show processlist 3 rows in set (0.00 sec)   ERROR: No query specified mysql> exit Bye 在Slave上做同样的操作，发现两个Yes则正常 =============================================================== 查看状态 及调试 1,查看master的状态SHOW MASTER STATUS; Position不应为0 2,查看slave的状态show slave status; Slave_IO_Running | Slave_SQL_Running这两个字段应为YES|YES. show processlist; 会有两条记录与同步有关state为Has read all relay log; waiting for the slave I/O thread to update it 和s Waiting for master to send event . 3,错误日志 MySQL安装目录\data\Hostname.err 4,CHANGE MASTER TO 如果A的Slave未启动,Slave_IO_Running为No. 可能会是B的master的信息有变化, 查看B SHOW MASTER STATUS; 记录下File,Position字段.假设为'mysql_binary_log.000004',98 ; 在A下执行: Stop Slave; CHANGE MASTER TO MASTER_LOG_FILE = 'mysql_binary_log.000004', MASTER_LOG_POS = 98 ; Start Slave; 5,SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n; 如果A的Slave_SQL_Running为No. Err文件中记录: Slave: Error 'Duplicate entry '1' for key 1' on query.... 可能是master未向slave同步成功,但slave中已经有了记录。造成的冲突. 可以在A上执行 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n; 跳过几步。再 restart salve;

1 2 下一页

【内容导航】
第1页：MySQL A/B复制试验	第2页：MySQL簇群集实验