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 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 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
[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 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 |
[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 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
[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 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
[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 8 9 10 11 12 13 |
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;"来启动同步。
在主数据库中添加一个数据库已经一些数据，然后在从数据库中看相关的数据是否已经存在，即可知道是否配置成功，最后再根据需要进行相应的配置优化。 🙂
No Comments