首页 > 代码库 > 搭建mysql主从服务器

搭建mysql主从服务器

搭建mysql主从服务器:

//注:注释掉my.cn文件中的skip-federated字段
[root@localhost ]# /bin/cp -r /usr/local/mysql/share/mysql/my-huge.cnf  /etc/my.cnf

配置主服务器:
修改my.cnf文件
server-id=1 //每个数据库服务器都要指定唯一一个server-id

log-bin=mysql-bin 

//mysql进行主从复制时通过二进制日志文件来进行的,所以必须开启mysql日志功能


授权给从服务器:
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘ztgame‘@‘172.30.207.20‘ IDENTIFIED BY ‘pwd‘;

查看主服务器状态
mysql> showmaster status;

配置从服务器
修改my.cnf文件
server-id=2
mast-host=172.30.207.19
mast-user=ztgame
mast-password=pwd
mast-port=3306
replicate-do-db=YCPDB //设置要同步的数据库,可以设置多个

从服务器数据库配置
mysql> slave stop;
mysql> GHANGE MASTER TO MASTER_LOG_FILE=‘mysql_bin.000001‘,MASTER_LOG_POS=106;
注:*** 这里的MASTER_LOG_FILE和MASTER_LOG_POS需要在master机器上查找:show master status;
mysql>slave start;

//查看同步情况
mysql> show slave status\G;
出现Slave_IO_Running:Yes和Slave_SQL_Running:Yes,就说明已经成功了。





====================================================================
locale查看编码

解决乱码
vim /etc/profile
export LC_ALL="zh_CN.GBK"
export LANG="zh_CN.GBK"
source /etc/profile

乱码的时候输入
echo -e "\xf"
领取签卡请求查看




mysql服务器的主从配置,这样可以实现读写分离,也可以在主库挂掉后从备用库中恢复

需要两台机器,安装mysql,两台机器要在相通的局域网内

主机A: 192.168.1.100

从机B:192.168.1.101

可以有多台从机

1、先登录主机 A

mysql>GRANT REPLICATION SLAVE ON *.* TO ‘backup’@’192.168.1.101‘ IDENTIFIED BY ‘123456’;

赋予从机权限,有多台丛机,就执行多次

2、 打开主机A的my.cnf,输入

server-id               = 1    #主机标示,整数
log_bin                 = /var/log/mysql/mysql-bin.log   #确保此文件可写
read-only              =0  #主机,读写都可以
binlog-do-db         =test   #需要备份数据,多个写多行
binlog-ignore-db    =mysql #不需要备份的数据库,多个写多行

3、打开从机B的my.cnf,输入

server-id               = 2
log_bin                 = /var/log/mysql/mysql-bin.log
master-host     =192.168.1.100
master-user     =backup
master-pass     =123456
master-port     =3306
master-connect-retry=60 #如果从服务器发现主服务器断掉,重新连接的时间差(秒)
replicate-do-db =test #只复制某个库
replicate-ignore-db=mysql #不复制某个库

4、同步数据库

只把主从库都启动即可自动同步,如果不嫌麻烦的话可以把主库的内容导出成SQL,然后在从库中运行一遍

5、先重启主机A的mysql,再重启从机B的mysql

6、验证

在主机A中,mysql>show master status\G;

在从机B中,mysql>show slave status\G;

能看到大致这些内容

File: mysql-bin.000001
Position: 1374
Binlog_Do_DB: test
Binlog_Ignore_DB: mysql

可以在主机A中,做一些INSERT, UPDATE, DELETE 操作,看看主机B中,是否已经被修改  


Mysql的主从复制至少是需要两个Mysql的服务,当然Mysql的服务是可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。

(1)首先确保主从服务器上的Mysql版本相同


(2)在主服务器上,设置一个从数据库的账户,使用REPLICATION SLAVE赋予权限,:


mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave001‘@‘192.168.0.99‘ IDENTIFIED BY ‘123456‘;


Query OK, 0 rows affected (0.13 sec)


(3)修改主数据库的配置文件my.cnf,开启BINLOG,并设置server-id的值,修改之后必须重启Mysql服务


[mysqld]

log-bin = /home/mysql/log/mysql-bin.log

server-id=1


(4)之后可以得到主服务器当前二进制日志名和偏移量,这个操作的目的是为了在从数据库启动后,从这个点开始进行数据的恢复


mysql> show master status\G;


*************************** 1. row ***************************

File: mysql-bin.000003

Position: 243

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)


(5)好了,现在可以停止主数据的的更新操作,并生成主数据库的备份,我们可以通过mysqldump导出数据到从数据库,当然了,你也可以直接用cp命令将数据文件复制到从数据库去


注意在导出数据之前先对主数据库进行READ LOCK,以保证数据的一致性


mysql> flush tables with read lock;


Query OK, 0 rows affected (0.19 sec)


之后是mysqldump


mysqldump -h127.0.0.1 -p3306 -uroot -p test > /home/chenyz/test.sql


最好在主数据库备份完毕,恢复写操作


mysql> unlock tables;


Query OK, 0 rows affected (0.28 sec)


(6)将刚才主数据备份的test.sql复制到从数据库,进行导入


(7)接着修改从数据库的my.cnf,增加server-id参数,指定复制使用的用户,主数据库服务器的ip,端口以及开始执行复制日志的文件和位置


[mysqld]

server-id=2

log_bin = /var/log/mysql/mysql-bin.log

master-host =192.168.1.100

master-user=test

master-pass=123456

master-port =3306

master-connect-retry=60

replicate-do-db =test 


(8)在从服务器上,启动slave进程


mysql> start slave;


(9)在从服务器进行show salve status验证


mysql> SHOW SLAVE STATUS\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: localhost

Master_User: root

Master_Port: 3306

Connect_Retry: 3

Master_Log_File: mysql-bin.003

Read_Master_Log_Pos: 79

Relay_Log_File: gbichot-relay-bin.003

Relay_Log_Pos: 548

Relay_Master_Log_File: mysql-bin .003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes


(10)好了,现在可以在我们的主服务器做一些更新的操作,然后在从服务器查看是否已经更新

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




1、主从服务器分别作以下操作
  1.1、版本一致
  1.2、初始化表,并在后台启动mysql
  1.3、修改root的密码

2、修改主服务器master:
   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=222       //[必须]服务器唯一ID,默认是1,一般取IP最后一段

3、修改从服务器slave:
   #vi /etc/my.cnf
       [mysqld]
       log-bin=mysql-bin   //[必须]启用二进制日志
       server-id=226       //[必须]服务器唯一ID,默认是1,一般取IP最后一段

4、重启两台服务器的mysql
   /etc/init.d/mysql restart

5、在主服务器上建立帐户并授权slave:
   #/usr/local/mysql/bin/mysql -uroot -pmttang   
   mysql>GRANT REPLICATION SLAVE ON *.* to ‘mysync‘@‘%‘ identified by ‘q123456‘; 


//一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如192.168.145.226,加强安全。

6、登录主服务器的mysql,查询master的状态
   mysql>show master status;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000004 |      308 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)
   注:执行完此步骤后不要再操作主服务器MYSQL,防止主服务器状态值变化

7、配置从服务器Slave:
   mysql>change master to aster_host=‘192.168.145.222‘,master_user=‘tb‘,master_password=‘q123456‘,
         master_log_file=‘mysql-bin.,000004‘,master_log_pos=308;   


//注意不要断开,“308”无单引号。

   Mysql>start slave;    //启动从服务器复制功能

8、检查从服务器复制功能状态:

   mysql> show slave status\G

   *************************** 1. row ***************************

                Slave_IO_State: Waiting for master to send event

                   Master_Host: 192.168.2.222  //主服务器地址

                   Master_User: myrync         //授权帐户名,尽量避免使用root

                   Master_Port: 3306           //数据库端口,部分版本没有此行

                 Connect_Retry: 60

               Master_Log_File: mysql-bin.000004

           Read_Master_Log_Pos: 600        //#同步读取二进制日志的位置,大于等于>=Exec_Master_Log_Pos

                Relay_Log_File: ddte-relay-bin.000003

                 Relay_Log_Pos: 251

         Relay_Master_Log_File: mysql-bin.000004

              Slave_IO_Running: Yes       //此状态必须YES

             Slave_SQL_Running: Yes       //此状态必须YES
                    ......

注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。

以上操作过程,主从服务器配置完成。
  
9、主从服务器测试:

主服务器Mysql,建立数据库,并在这个库中建表插入一条数据:

  mysql> create database hi_db;
  Query OK, 1 row affected (0.00 sec)

  mysql> use hi_db;
  Database changed

  mysql>  create table hi_tb(id int(3),name char(10));
  Query OK, 0 rows affected (0.00 sec)
 
  mysql> insert into hi_tb values(001,‘bobu‘);
  Query OK, 1 row affected (0.00 sec)

  mysql> show databases;
   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | hi_db              |
   | mysql              |
   | test               |
   +--------------------+
   4 rows in set (0.00 sec)

从服务器Mysql查询:

   mysql> show databases;

   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | hi_db              |          //I‘M here,大家看到了吧
   | mysql              |
   | test               |
   +--------------------+
   4 rows in set (0.00 sec)

   mysql> use hi_db
   Database changed
   mysql> select * from hi_tb;    //可以看到在主服务器上新增的具体数据
   +------+------+
   | id   | name |
   +------+------+
   |    1 | bobu |
   +------+------+
   1 row in set (0.00 sec)

搭建mysql主从服务器