mysql-5.5.31-CentOS6.4-X86_64数据库主从同步
系统环境信息：CentOS6.4-X86_64
MySQL Master Server IP: 10.10.0.202
MySQL Slave Server IP : 10.10.0.203
一:主从数据库安装:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | [root@localhost ~]# yum upgrade -y [root@localhost ~]# yum install -y popt-devel popt-static libnl-devel bison-devel bison bison-runtime kernel-devel [root@localhost ~]# yum install -y autoconf213.noarch openssl-devel gcc* make cmake libtool ncurses ncurses-devel [root@localhost ~]# yum install -y vim lsof nmap curl wget [root@localhost ~]# [root@localhost ~]# groupadd mysql [root@localhost ~]# useradd -g mysql -d /dev/null -s /sbin/nologin mysql [root@localhost ~]# mkdir -p /data0/mysql/database //数据库文件存放目录 [root@localhost ~]# mkdir -p /data0/source/ && cd /data0/source/ //源码包存放目录 [root@localhost ~]# [root@localhost ~]# wget http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.31.tar.gz [root@localhost ~]# tar zxvf mysql-5.5.31.tar.gz && cd mysql-5.5.31 [root@localhost ~]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql/ -DMYSQL_DATADIR=/data0/mysql/database -DMYSQL_UNIX_ADDR=/usr/local/mysql/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_USER=mysql -DMYSQL_GROUP=mysql -DWITH_DEBUG=0 [root@localhost ~]# make && make install [root@localhost ~]# chown mysql:mysql /usr/local/mysql [root@localhost ~]# /usr/local/mysql/scripts/mysql_install_db \ --user=mysql --basedir=/usr/local/mysql \ --datadir=/data0/mysql/database //初始化数据库 [root@localhost ~]# chown mysql:root /data0/mysql/database [root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@localhost ~]# cp support-files/my-innodb-heavy-4G.cnf /usr/local/local/my.cnf [root@localhost ~]# chmod +x /etc/init.d/mysqld [root@localhost ~]# chkconfig --level 345 mysqld on [root@localhost ~]# chkconfig mysqld on [root@localhost ~]# service mysqld restart [root@localhost ~]# export PATH=/usr/local/mysql/bin:$PATH [root@localhost ~]# ln -s /usr/lib64/mysql/libmysqlclient.so.16.0.0 /usr/lib/libmysqlclient.so [root@localhost ~]# mysql -u root -p mysql> use mysql; mysql> update user set password=PASSWORD("admin") where user='root'; //设置密码，此处为设置复杂的密码 mysql> update user set host="%" where host="::1"; //运行远程连接，仅供测试用 mysql> delete user from user where user=''; //删除用户名为空的用户 mysql> drop database test; //删除测试用的数据库 mysql> exit; [root@localhost ~]# /etc/init.d/mysqld restart [root@localhost ~]# [root@localhost ~]# |
二:主数据库(10.10.0.202)上配置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | [root@localhost ~]# mysql -u root -p //创建一个具有REPLICATION SLAVE权限的账户 mysql> GRANT REPLICATION SLAVE,REPLICATION CLIENT,RELOAD,SUPER ON *.* TO 'backup'@'10.10.0.203' IDENTIFIED BY 'admin'; Query OK, 0 rows affected (0.03 sec) mysql> use mysql; Database changed mysql> select user,host from user; +--------+-----------------------+ | user | host | +--------+-----------------------+ | root | % | | root | 127.0.0.1 | | backup | 10.10.0.203 | | root | localhost | | root | localhost.localdomain | +--------+-----------------------+ 5 rows in set (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) mysql> exit Bye [root@localhost ~]# service mysqld stop Shutting down MySQL.... SUCCESS! [root@localhost ~]# |
编辑MySQL Master Server的my.cnf 配置文件,在[mysqld]段中加入如下信息，全文查找server-id去掉重复的此设置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | [root@localhost ~]# vim /etc/my.cnf skip-name-resolve server-id = 1 replicate-ignore-db=mysql,information_schema,performance_schema //不用同步的数据库列表，多个以逗号分隔 log-slave-updates slave-skip-errors=all sync_binlog=1 auto_increment_increment=2 auto_increment_offset=1 [root@localhost ~]# /etc/init.d/mysqld restart [root@localhost ~]# mysql -u root -p mysql> show master status; //出现如下的信息表示主服务器配置成功 +------------------+-----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+-----------+--------------+------------------+ | mysql-bin.000010 | 238506805 | | mysql | +------------------+-----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> |
三:从数据库(10.10.0.203)上配置:
同主服务器配置文件里面一样，编辑 my.cnf 配置文件,在 [mysqld] 段中加入如下信息，全文查找server-id去掉重复的此设置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 | [root@localhost ~]# vim /etc/my.cnf server-id= 2 replicate-ignore-db=mysql,information_schema,performance_schema log-slave-updates slave-skip-errors=all sync_binlog=1 auto_increment_increment=2 auto_increment_offset=2 [root@localhost ~]# /etc/init.d/mysqld restart [root@localhost ~]# mysql -u root -p mysql> show master status; +------------------+-----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+-----------+--------------+------------------+ | mysql-bin.000006 | 415880065 | | mysql | +------------------+-----------+--------------+------------------+ 1 row in set (0.02 sec) mysql> CHANGE MASTER TO -> MASTER_HOST='10.10.0.202', //主服务器的IP。 -> MASTER_USER='backup', //配置主服务器时建立的用户名 -> MASTER_PASSWORD='admin', //用户密码 -> MASTER_LOG_FILE='mysql-bin.000006', //此处的名字是在从服务器端执行SHOW MASTER STATUS; 显示的第一列的那个file列 -> MASTER_LOG_POS=107, //在从服务器端执行SHOW MASTER STATUS; 显示的第二列的那个Position值 -> MASTER_PORT=3306; //主服务器mysql端口，如果未曾修改，默认即可 mysql> slave start; mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.202 Master_User: backup Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000010 Read_Master_Log_Pos: 238507082 Relay_Log_File: localhost-relay-bin.000010 Relay_Log_Pos: 238507228 Relay_Master_Log_File: mysql-bin.000010 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql 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: 238507082 Relay_Log_Space: 238517397 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) ERROR: No query specified mysql> exit; |
遇到这样的错误如：
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
等或由于清数据导致主从库不同步了，解决办法如下：
先进入slave中执行:"slave stop;"来停止从库同步；再去master中执行:"flush logs;"来清空日志；然后在master中执行:"show master status;"查看下主库的状态，主要是日志的文件和position；然后回到slave中，执行:
1 2 3 4 5 6 7 | mysql> CHANGE MASTER TO -> MASTER_HOST='10.10.0.202', -> MASTER_USER='backup', -> MASTER_PASSWORD='admin', -> MASTER_LOG_FILE='mysql-bin.000006', -> MASTER_LOG_POS=107, -> MASTER_PORT=3306; |
文件和位置对应master中的;最后在slave中执行:"slave start;"来启动同步。
在主数据库中添加一个数据库已经一些数据，然后在从数据库中看相关的数据是否已经存在，即可知道是否配置成功，最后再根据需要进行相应的配置优化。 🙂