mysql5.5.25“互为主从”正式部署安装详解

最新推荐文章于 2022-07-06 00:10:44 发布

kjh2007abc

最新推荐文章于 2022-07-06 00:10:44 发布

阅读量147

点赞数

本文链接：https://blog.csdn.net/kjh2007abc/article/details/85001264

版权

1.mysql5.5.25编译安装部署，见：http://michaelkang.blog.51cto.com/1553154/935133

2.安装简介

2.1:安装环境

mysql.master:192.168.16.30

[root@testserver03 ~]# uname -a
Linux testserver03 2.6.32-279.2.1.el6.x86_64 #1 SMP Fri Jul 20 01:55:29 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

[root@testserver03 ~]# more /etc/redhat-release
CentOS release 6.3 (Final)

内存：16G

CPU:Intel(R) Xeon(R) CPU E5620 @ 2.40GHz

mysql.slave:192.168.16.31

[root@test04 ~]# uname -a
Linux test04 2.6.32-220.el6.x86_64 #1 SMP Tue Dec 6 19:48:22 GMT 2011 x86_64 x86_64 x86_64 GNU/Linux

[root@test04 ~]# more /etc/redhat-release
CentOS release 6.3 (Final)

内存：16G

CPU:Intel(R) Xeon(R) CPU E5620 @ 2.40GHz

2.2：用户和目录环境
mysql.master:192.168.16.30
mysql.slave:192.168.16.31
用户名：mysql
安装目录：/mysql
数据库目录：/data/mysqldata
安装文件存放目录：/workspace
源码包：mysql-5.5.25.tar.gz
mysql密码（123）
本地登录方式：
mysql -h 127.1 -u root –p123

3.修改主从服务器配置文件；

master 服务器配置文件如下：

[root@testserver03 ~]# more /etc/my.cnf
[client]
user            = root
password        = 123
port            = 3306
socket          = /tmp/mysqld.sock
[mysqld]
#character-set-server = utf8
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
user    = mysql
port            = 3306
socket          = /tmp/mysqld.sock
basedir = /mysql
datadir = /data/mysqldata
log-error = /var/log/mysql_error.log
pid-file = /data/mysqldata/test04.pid
skip-external-locking = 0
expire_logs_days = 1
max_connect_errors = 6000
back_log = 600
max_connections = 5000
max_connect_errors = 200
table_open_cache = 2048
max_allowed_packet = 32M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 300
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 2
server-id = 1
key_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 6G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 6G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

slave 服务器配置文件如下：

[root@test04 ~]# more /etc/my.cnf
[client]
#character-set-server = utf8
user            = root
password        = 123
port            = 3306
socket          = /tmp/mysqld.sock
[mysqld]
#character-set-server = utf8
replicate-ignore-db = mysql
replicate-ignore-db = test
replicate-ignore-db = information_schema
user    = mysql
port            = 3306
socket          = /tmp/mysqld.sock
basedir = /mysql
datadir = /data/mysqldata
log-error = /var/log/mysql_error.log
pid-file = /data/mysqldata/test04.pid
skip-external-locking = 0
expire_logs_days = 1
max_connect_errors = 6000
back_log = 600
max_connections = 5000
max_connect_errors = 200
table_open_cache = 2048
max_allowed_packet = 32M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 300
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
log-bin=mysql-bin
binlog_format=mixed
slow_query_log
long_query_time = 2
server-id = 2
key_buffer_size = 64M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 6G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 6G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
open-files-limit = 8192

将修改完成的配置文件，上传到主备服务器，server id 主改为1 slave改为2.

配置文件改的匆忙，如果有不妥的地方，各位大侠看到，一定要指点一下！

主备服务器都成功启动后，下面正式开始配置互为主从。

进入数据库，在 master 为辅库添加同步用户，在 slave上为辅库添加同步用户；

在master上执行

grant replication slave on *.* to 'master'@'192.168.16.%' identified by '123';

flush privileges;

在slave上执行
grant replication slave on *.* to 'slave'@'192.168.16.%' identified by '123';

flush privileges;

2.测试账户有效性服务ID的唯一；

2.1
在master上执行
/mysql/bin/mysql -h 192.168.16.31 -u slave -pp123

2.2
在slave上执行
/mysql/bin/mysql -h 192.168.16.30 -u master -p123

3.锁住主库表，停止数据更新。

进入到mysql后输入
mysql> flush tables with read lock;

此时所有数据库内的表只提供读的操作，不能再写入。如果主数据库有大量数据，进行数据同步，把现有主库的数据导入到从库，数据导入完成.

4.首先配置主从模式；

4.1
在master上执行
查看一下库状态；

4.2
在slave上执行，配置需要同步的master服务器的相关信息；

change master to master_host='192.168.16.30',master_user='master', master_password='123',master_log_file='mysql-bin.000011',master_log_pos=107;

【注意】
192.168.16.30         ---masterDB的IP
master                  ---为主从同步需要的用户名及密码
mysql-bin.000011      ---在master上执行show master status的log文件名
107           ---需要同步的起点位置（在master上执行show master status得出该结果）

4.3.开启同步进程；

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

4.4.查看只从同步状态；

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.16.30
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000011
          Read_Master_Log_Pos: 107
               Relay_Log_File: test04-relay-bin.000002
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000011
             Slave_IO_Running: Yes            #这两个值为yes只从同步正常；
            Slave_SQL_Running: Yes                 #

4.5
在master上执行，数据库解锁。

unlock tables;

5.测试主从同步；

5.1.在master建立pttest库；
mysql> create database pttest;
Query OK, 1 row affected (0.00 sec)

6.开始配置互为主从；

6.1
在slave上执行
mysql -h 127.1 -u root -p123 登录slave 数据库；

6.2.锁住主库表，停止数据更新。

进入到mysql后输入
mysql> flush tables with read lock;

查看一下库状态；

6.3.
在master上执行

change master to master_host='192.168.16.31',master_user='slave', master_password='p123',master_log_file='mysql-bin.000005',master_log_pos=107;

【注意】
192.168.16.31         ---slaveDB的IP
master                  ---为主从同步需要的用户名及密码
mysql-bin.000005      ---在master上执行show master status的log文件名
107             ---需要同步的起点位置（在master上执行show master status得出该结果）

6.4.开启同步slave进程；

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

6.5.查看只从同步状态；

6.6
在slave上执行，数据库解锁。

unlock tables;

7.测试主从同步；

7.1.在master建立pttest库；
mysql> create database slavetest;
Query OK, 1 row affected (0.00 sec)

8.互为主从测试；

在主服务器上建立mysqltest数据库，分别在mysql和slave上进行数据插入和查询，得到结果始终保持一致；

8.1
在master上执行；

编辑创建数据库和表的脚本

SHOW DATABASES;
CREATE DATABASE IF NOT EXISTS mysqltest;
USE mysqltest;
CREATE TABLE IF NOT EXISTS sbook (
id             INT(10) PRIMARY KEY NOT NULL AUTO_INCREMENT
,name           varchar(20) NOT NULL DEFAULT 'change_name_here'
,gender         ENUM('M', 'F', 'U') NOT NULL DEFAULT 'M'
,birthday       DATE NOT NULL DEFAULT '0000-00-00'
);

SHOW TABLES;
SELECT * FROM sbook;

8.2
master 上数据插入；
USE mysqltest;
INSERT INTO sbook ( name, gender, birthday ) VALUES ('tubie', 'F', '2010-07-15');
INSERT INTO sbook VALUES ( '', 'haolei', 'M', '2010-05-25' );

8.3
master 上查看sbook表内容；

8.4
slave数据库上查看mysqltest库和sbook表内容；

mysql> use mysqltest;
Database changed
mysql> select * from sbook;
+----+--------+--------+------------+
| id | name   | gender | birthday   |
+----+--------+--------+------------+
| 1 | tubie | F      | 2010-07-15 |
| 2 | haolei | M      | 2010-05-25 |   #表内容一致；
+----+--------+--------+------------+
2 rows in set (0.00 sec)

8.5
在slave上插入数据，然后再master上查看，验证数据是否一致；

8.6
slave 上数据插入；
USE mysqltest;
INSERT INTO sbook ( name, gender, birthday ) VALUES ('tubie', 'F', '2010-07-15');
INSERT INTO sbook VALUES ( '', 'haolei', 'M', '2010-05-25' );

8.7
slave 上查看sbook表内容；

mysql> SELECT * FROM sbook;
+----+--------+--------+------------+
| id | name   | gender | birthday   |
+----+--------+--------+------------+
| 1 | tubie | F      | 2010-07-15 |
| 2 | haolei | M      | 2010-05-25 |
| 3 | tubie | F      | 2010-07-15 |
| 4 | haolei | M      | 2010-05-25 |
| 5 | tubie | F      | 2010-07-15 |
| 6 | haolei | M      | 2010-05-25 |
| 7 | tubie | F      | 2010-07-15 |
| 8 | haolei | M      | 2010-05-25 |
+----+--------+--------+------------+
8 rows in set (0.00 sec)

8.8
master上查看sbook表内容；
mysql> SELECT * FROM sbook;
+----+--------+--------+------------+
| id | name   | gender | birthday   |
+----+--------+--------+------------+
| 1 | tubie | F      | 2010-07-15 |
| 2 | haolei | M      | 2010-05-25 |
| 3 | tubie | F      | 2010-07-15 |
| 4 | haolei | M      | 2010-05-25 |
| 5 | tubie | F      | 2010-07-15 |
| 6 | haolei | M      | 2010-05-25 |
| 7 | tubie | F      | 2010-07-15 |
| 8 | haolei | M      | 2010-05-25 |
+----+--------+--------+------------+
8 rows in set (0.00 sec)

结论：分别在主数据库和从数据库上进行数据插入，分别查询表内容，主从库上内容始终保持一致，互为主从功能实现；