昨天同步设置有一个问题,今天我用mysql版本5.5.25的去做,前面都一样,主要看配置和后面的设置


前期准备:关闭防火墙

关闭SELINUX

关闭SELINUX

vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq  #保存退出

setenforce 0 #立即生效

                                     -------两台机器都要关闭防火墙和selinux

如果一定要开启防火墙的话,建议只需要加入3306端口就行,具体方法见我的另外一个文章(http://caizi.blog.51cto.com/5234706/1536463);

环境:linux虚拟机两台,系统是centos 6.3 64位

                       mysql 版本:5.5.25 linux 64位

两台服务器

主:172.19.0.116     Master116          主(master)

从:172.19.0.117      backup117        从(saver)

两台都要安装mysql如下步骤

安装系统所需要的依赖包

[root@ 116 ~]# yum -y install gcc gcc-c++ gcc-g77 autoconf automake zlib* fiex* libxml* ncurses-devel libmcrypt* libtool-ltdl-devel*  cmake  bison

 

创建mysql用户与用户组

[root@ 116~]# groupadd mysql

[root@ 116~]# useradd -r -g mysql mysql

 

安装mysql

[root@ 116~]# tar zxvf mysql-5.5.25.tar.gz

[root@ 116~]# cd mysql-5.5.25

[root@116 mysql-5.5.25]# cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql/ -DMYSQL_DATADIR=/opt/mysql/data -DMYSQL_UNIX_ADDR=/opt/mysql/data/mysqld.sock -DWITH_INNOBASE_STORAGE_ENGINE=1  -DENABLED_LOCAL_INFILE=1 -DMYSQL_TCP_PORT=3306 -DEXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_UNIX_ADDR=/opt/mysql/data/mysql.sock -DMYSQL_USER=mysql  -DWITH_DEBUG=0

 

[root@ 116   mysql-5.5.25]# make && make install

 

赋予相关权限

[root@ 116 mysql-5.5.25]# chown -R mysql:mysql /opt/mysql

 

 

[root@116 mysql-5.5.25]#  /opt/mysql/scripts/mysql_install_db --user=mysql --basedir=/opt/mysql   --datadir=/opt/mysql/data

 

mysql配置文件

[root@ 116  mysql-5.5.25]# cp /opt/mysql/support-files/my-large.cnf /etc/my.cnf     ----部分文章my--large.cnf不一样,因为   版本不同

[root@116 mysql-5.5.25]# cp /opt/mysql/support-files/mysql.server /etc/init.d/mysql

[root@116 mysql-5.5.25]# chmod +x /etc/init.d/mysql

 

增加自动启动

[root@116 mysql-5.5.25]# chkconfig mysql on

 

启动mysql

[root@116  mysql-5.5.25]# /etc/init.d/mysql start

Starting MySQL......                                       [  OK  ]

 

查看mysql 3306端口,看到下面说明启动成功

[root@116  mysql-5.5.25]#ps aux | grep 3306

mysql    17237  3.6  8.4 615660 86264 pts/2    Sl   22:10   0:01 /opt/mysql/bin/mysqld --basedir=/opt/mysql

 

 

设置mysql初始密码,123456是密码,你可以设置自己需要的密码

[root@116 mysql-5.5.25]# /opt/mysql/bin/mysqladmin -u root password '123456'

 

 

[root@116  mysql-5.5.25]# /opt/mysql/bin/mysql -uroot -p123456

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.25-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

           ----红色为系统显示

mysql>


主从复制的配置

主的my.cnfserver-id=1,每个同步服务都必须设定一个唯一的编号。

路径:cat /etc/my.cnf   查看my.cnf 内容

参数详解:

server-id=1

#binlog-do-db=repl //需要同步的数据库,如果没有本行,即表示同步所有的数据库

#binlog-ignore-db=mysql //被忽略的数据库


修改之后一定要重启mysql服务。

[root@localhost ~]# /etc/init.d/mysql restart


show variables like 'server_id';  #查看server-id的值是否为1

show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

mysql> show master status;    
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Master(这里为116机器)上增加一个用于复制的账号:

mysql>GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'172.19.0.%'  IDENTIFIED BY '123456';

mysql>flush privileges;     --这个地方注意:有的帖子写的是(mysql>FLUSH REPLICATION;)报错

mysql> show master status;           - --- 这里没显示不知道为啥




设置Slave 主机

修改my.cnfserver-id,内容如下:

路径:cat /etc/my.cnf   查看my.cnf 内容


backup117主机

server-id=2




show variables like 'server_id';  #查看server-id的值是否为2

mysql>  show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.02 sec)



开启MasterSlave的同步

Slave上执行如下命令

mysql>CHANGE MASTER TO MASTER_HOST='172.19.0.116',

         ->MASTER_USER='repl',

         ->MASTER_PASSWORD='123456',

         ->MASTER_LOG_FILE='mysql-bin.000001',

         ->MASTER_LOG_POS=245;

 之后执行                             ----这个地方执行是一个块,我卡这里了

 mysql>start slave;      ###开启服务器线程                     ----(不是其他文章里面的slave start;执行slave start;会报错的)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.19.0.116
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

              Replicate_Do_DB:
          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: 107
              Relay_Log_Space: 787
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)


看到上面红色字体自后,恭喜你你成功了!

接下来我们要做测试,数据测试!

在主服务器上找到需要同步的数据库,

#binlog-do-db=repl //需要同步的数据库,如果没有本行,即表示同步所有的数据库

#binlog-ignore-db=mysql //被忽略的数据库

如果上面的注释掉 ,就默认为要同步所有的数据库

我们以test数据库为例:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      107 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
我们同步test数据库。在/etc/my.cnf上做了配置

binlog-do-db=test

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

mysql> use test;      ----切换到test数据库
Database changed
mysql> create table fangwei(id int,name char(10));       ----建表,表明fangwei
Query OK, 0 rows affected (0.22 sec)

mysql> insert into fangwei values(1,'xiaoyang');     ------  下面是插入五条数据
Query OK, 1 row affected (0.07 sec)

mysql> insert into fangwei values(2,'mobei');
Query OK, 1 row affected (0.01 sec)

mysql> insert into fangwei values(3,'hujingtao');
Query OK, 1 row affected (0.01 sec)

mysql> insert into fangwei values(4,'zhoujielun');
Query OK, 1 row affected (0.01 sec)

mysql> insert into fangwei values(5,'liuyifei');
Query OK, 1 row affected (0.01 sec)

mysql> select * from fangwei;       ----查询fangwei表
+------+------------+
| id   | name       |
+------+------------+
|    1 | xiaoyang   |
|    2 | mobei      |
|    3 | hujingtao  |
|    4 | zhoujielun |
|    5 | liuyifei   |
+------+------------+
5 rows in set (0.01 sec)


我们去117上从服务器上查询

mysql> show databases;                   ----查询所有数据库名
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use test;                        -----切换数据库
Database changed
mysql> select * from fangwei;      ---查询刚才我们在主服务器建的表
+------+------------+
| id   | name       |
+------+------------+
|    1 | xiaoyang   |
|    2 | mobei      |
|    3 | hujingtao  |
|    4 | zhoujielun |
|    5 | liuyifei   |
+------+------------+
5 rows in set (0.00 sec)


可以看到,在从服务器上也能查到刚才在主服务器上建的表,和表的内容,说明我们的主从同步完成了!


注意事项:

1,关闭防火墙

2,关闭selinux

3,配置/etc/my.cnf时,里面的参数需要注意,每个参数都有不同的 含义

4,修改mysql配置时,一定要重启mysql服务。

/etc/init.d/mysql restart

5,有的博客里面的有错误,需要自己去斧正!如博主笔误等。