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 – Debugbaseurl=file:///media/Serverenabled=1gpgcheck=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 -p123mysql> use test;Database changedmysql> 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 namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> 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> exitBye修改mysql主配置文件/etc/my.conf在[mysqld]中加入以下内容[root@localhost ~]# service mysqld stop //先停止MYSQL ,修改完毕后启用停止 MySQL： [确定][root@localhost ~]# vi /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=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 -p123mysql> use test;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> 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 namesYou can turn off this feature to get a quicker startup with -A Database changedmysql> 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> exitBye修改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/mysqlsocket=/var/lib/mysql/mysql.sockuser=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 -p123Mysql>show master status;mysql>show slve statusG;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 statusG;*************************** 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: NULL1 row in set （0.00 sec） ERROR: No query specified表示正常！到此mysql的双机互备已基本完成，在两台服务器的mysql数据库中任意添加数据，都可以同步到对端服mysql> show processlistG;*************************** 1. row *************************** Id: 2 User: system user Host: db: NULLCommand: Connect Time: 422 State: Connecting to master Info: NULL*************************** 2. row *************************** Id: 3 User: system user Host: db: NULLCommand: 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: NULLCommand: Query Time: 0 State: NULL Info: show processlist3 rows in set （0.00 sec） ERROR: No query specifiedmysql> exitBye在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安装目录dataHostname.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页：MySQL A/B复制试验	第2页：MySQL簇群集实验

RHEL6.0下Oracle安装Oracle 服务自启动相关资讯 MySQL AB复制

MySQL AB复制详述（08/20/2015 10:38:21）

本文评论查看全部评论（0）