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

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

1.mysql5.5.25编译安装部署，见：​​~]# 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-f [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-f [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.

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

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

1.

进入数据库，在 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

mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id     | 2     |   #主备服务器id不能相同，如果相同无法进行日志同步。 +---------------+-------+ 1 row in set (0.00 sec)

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

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

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

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

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

4.首先配置主从模式；

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

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

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                 #

mysql> show processlist; 查看日主同步，复制情况；          | | 19 | system user |                 | NULL | Connect     |  229 | Waiting for master to send event       #提示等待从master上发送日志                      |

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

unlock tables;

5.测试主从同步；

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

5.2 在slave上查看； mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | pttest             |  #主库上新建的表，已经同步过来！ | test               | +--------------------+ 5 rows in set (0.00 sec)

6.开始配置互为主从；

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

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

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

查看一下库状态；

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

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.查看只从同步状态；

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                 # mysql> show processlist; 查看日主同步，复制情况；          | | 19 | system user |                 | NULL | Connect     |  229 | Waiting for master to send event       #呵呵，你懂得！                       |

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

unlock tables;

7.测试主从同步；

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

7.2 在master上查看； mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | performance_schema | | slavetest             |  #主库上新建的表，已经同步过来！ | test               | +--------------------+ 5 rows in set (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表内容；

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.4 slave数据库上查看mysqltest库和sbook表内容；

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | mysql              | | mysqltest          |#数据库已经生成； | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec)

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)

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。