mysql 5.5数据库主从配置步骤详解

小明SEO博客 小明SEO博客，新时代SEO博客

上次给大家介绍了mysql 5.1主从搭建配置教程,这次我们来实现mysql 5.5的主从复制,其实大体上配置是差不多的,只有点细微的差别.

系统:centos 5.x,需要的软件包:mysql-5.5.33.tar.gz

环境准备:服务器a:192.168.10.151 (主),服务器b:192.168.10.152 (从)

1.安装前准备

  1. wgethttp://mysql.llarian.net/Downloads/MySQL-5.5/mysql-5.5.33.tar.gz
  2. yum-yinstallgccgcc-c++libtool-libsautoconffreetype-develgdlibjpeg-devel
  3. libpng-devellibxml2-develncurses-develzlib-develzipunzipcurl-develwgetcrontabs
  4. filebisoncmakepatchmlocateflexdiffutilsautomakemakekernel-develcpp
  5. readline-developenssl-develvim-minimalglibc-develglib2-devel
  6. bzip2-devele2fsprogs-devellibidn-develgettext-develexpat-devel
  7. libcap-devellibtool-ltdl-develpam-develpcre-devellibmcrypt-devel

2.在服务器a上安装mysql

  1. tarzxfmysql-5.5.33.tar.gz&&cdmysql-5.5.33
  2. /usr/sbin/groupaddmysql
  3. /usr/sbin/useradd-gmysqlmysql
  4. cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mysql-DMYSQL_UNIX_ADDR=/tmp/mysql.sock-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DWITH_EXTRA_CHARSETS=complex-DWITH_READLINE=1-DENABLED_LOCAL_INFILE=1
  5. make
  6. makeinstall
  7. chmod+w/usr/local/mysql
  8. chown-Rmysql:mysql/usr/local/mysql
  9. cdsupport-files/
  10. cpmy-medium.cnf/etc/my.cnf
  11. cp-fmysql.server/etc/rc.d/init.d/mysqld
  12. mkdir/var/lib/mysql
  13. /usr/local/mysql/scripts/mysql_install_db--defaults-file=/etc/my.cnf--basedir=/usr/local/mysql--datadir=/var/lib/mysql--user=mysql
  14. chmod+x/etc/rc.d/init.d/mysqld
  15. chkconfig--addmysqld
  16. chkconfigmysqldon
  17. cat>/etc/ld.so.conf.d/mysql.conf<<EOF
  18. /usr/local/mysql/lib/mysql
  19. /usr/local/lib
  20. EOF
  21. ldconfig

如果你是在64位机器上的话,那你就执行下面这个命令:

ln -s /usr/local/mysql/lib/mysql /usr/lib64/mysql

如果你是在32位机器上的话,那就执行下面的命令:

ln -s /usr/local/mysql/lib/mysql /usr/lib/mysql

然后再执行:

  1. ln-s/usr/local/mysql/bin/mysql/usr/bin
  2. ln-s/usr/local/mysql/bin/mysqladmin/usr/bin
  3. ln-s/usr/local/mysql/bin/mysqldump/usr/bin
  4. servicemysqldstart
  5. mysqladmin-urootpassword'password'

当然如果大家觉得编译安装mysql很麻烦的话,那么可以去看看这篇文章安装mysql5.5二进制包,服务器b安装mysql配置跟服务器a一样,这里就不说了.

3.配置主从,在服务器a上:

  1. vi/etc/my.cnf
  2. [mysqld]
  3. log-bin=master-bin
  4. log-bin-index=master-bin.index
  5. server-id=1
  6. innodb_file_per_table=1
  7. binlog_format=mixed

授权复制用户:

  1. mysql-uroot-p
  2. grantreplicationslaveon*.*to'dbmysql'@'%'identifiedby'123456';
  3. flushprivileges;

重启mysql:service mysqld restart

在服务器b上:

  1. vi/etc/my.cnf
  2. [mysqld]
  3. relay-log=relay-log
  4. relay-log-index=relay-log.index
  5. server-id=2
  6. innodb_file_per_table=1
  7. binlog_format=mixed

然后重启mysql:service mysqld restart

服务器b连接连接主服务器并复制,先在服务器a上查看master的状态:

  1. mysql>showmasterstatus;
  2. +-------------------+----------+--------------+------------------+
  3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
  4. +-------------------+----------+--------------+------------------+
  5. |master-bin.000001|107|||
  6. +-------------------+----------+--------------+------------------+
  7. 1rowinset(0.00sec)

然后在服务器b上进行连接:

  1. mysql>changemastertomaster_host='192.168.10.151',master_user='dbmysql',maste
  2. r_password='123456',master_log_file='master-bin.000001',master_log_pos=107;

查看一下slave状态,代码如下:

  1. mysql>showslavestatusG
  2. ***************************1.row***************************
  3. Slave_IO_State:
  4. Master_Host:192.168.10.151
  5. Master_User:dbmysql
  6. Master_Port:3306
  7. Connect_Retry:60
  8. Master_Log_File:master-bin.000001
  9. Read_Master_Log_Pos:107
  10. Relay_Log_File:localhost-relay-bin.000001
  11. Relay_Log_Pos:4
  12. Relay_Master_Log_File:master-bin.000001
  13. Slave_IO_Running:No
  14. Slave_SQL_Running:No
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB:
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno:0
  22. Last_Error:
  23. Skip_Counter:0
  24. Exec_Master_Log_Pos:107
  25. Relay_Log_Space:107
  26. Until_Condition:None
  27. Until_Log_File:
  28. Until_Log_Pos:0
  29. Master_SSL_Allowed:No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master:NULL
  36. Master_SSL_Verify_Server_Cert:No
  37. Last_IO_Errno:0
  38. Last_IO_Error:
  39. Last_SQL_Errno:0
  40. Last_SQL_Error:--phpfensi.com
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id:0
  43. 1rowinset(0.00sec)

启动slave再查看:

  1. mysql>startslave;
  2. QueryOK,0rowsaffected(0.01sec)
  3. mysql>showslavestatusG
  4. ***************************1.row***************************
  5. Slave_IO_State:Connectingtomaster
  6. Master_Host:192.168.10.151
  7. Master_User:dbmysql
  8. Master_Port:3306
  9. Connect_Retry:60
  10. Master_Log_File:master-bin.000001
  11. Read_Master_Log_Pos:107
  12. Relay_Log_File:localhost-relay-bin.000001
  13. Relay_Log_Pos:4
  14. Relay_Master_Log_File:master-bin.000001
  15. Slave_IO_Running:Connecting
  16. Slave_SQL_Running:Yes
  17. Replicate_Do_DB:
  18. Replicate_Ignore_DB:
  19. Replicate_Do_Table:
  20. Replicate_Ignore_Table:
  21. Replicate_Wild_Do_Table:
  22. Replicate_Wild_Ignore_Table:
  23. Last_Errno:0
  24. Last_Error:
  25. Skip_Counter:0
  26. Exec_Master_Log_Pos:107
  27. Relay_Log_Space:107
  28. Until_Condition:None
  29. Until_Log_File:
  30. Until_Log_Pos:0
  31. Master_SSL_Allowed:No
  32. Master_SSL_CA_File:
  33. Master_SSL_CA_Path:
  34. Master_SSL_Cert:
  35. Master_SSL_Cipher:
  36. Master_SSL_Key:
  37. Seconds_Behind_Master:NULL
  38. Master_SSL_Verify_Server_Cert:No
  39. Last_IO_Errno:2003
  40. Last_IO_Error:errorconnectingtomaster'dbmysql@192.168.10.151:3306'-retry-time:60retries:86400
  41. Last_SQL_Errno:0
  42. Last_SQL_Error:
  43. Replicate_Ignore_Server_Ids:
  44. Master_Server_Id:0
  45. 1rowinset(0.00sec)

看到上面错误了没,说明mysql主和从服务器的防火墙没有开放3306端口,去服务器a和服务器b上把3306端口打开,再来查看mysql从的状态,代码如下:

  1. mysql>showslavestatusG
  2. ***************************1.row***************************
  3. Slave_IO_State:Waitingformastertosendevent
  4. Master_Host:192.168.10.151
  5. Master_User:dbmysql
  6. Master_Port:3306
  7. Connect_Retry:60
  8. Master_Log_File:master-bin.000001
  9. Read_Master_Log_Pos:107
  10. Relay_Log_File:relay-log.000011
  11. Relay_Log_Pos:254
  12. Relay_Master_Log_File:master-bin.000001
  13. Slave_IO_Running:Yes
  14. Slave_SQL_Running:Yes
  15. Replicate_Do_DB:
  16. Replicate_Ignore_DB:
  17. Replicate_Do_Table:
  18. Replicate_Ignore_Table:
  19. Replicate_Wild_Do_Table:
  20. Replicate_Wild_Ignore_Table:
  21. Last_Errno:0
  22. Last_Error:
  23. Skip_Counter:0
  24. Exec_Master_Log_Pos:107
  25. Relay_Log_Space:404
  26. Until_Condition:None
  27. Until_Log_File:
  28. Until_Log_Pos:0
  29. Master_SSL_Allowed:No
  30. Master_SSL_CA_File:
  31. Master_SSL_CA_Path:
  32. Master_SSL_Cert:
  33. Master_SSL_Cipher:
  34. Master_SSL_Key:
  35. Seconds_Behind_Master:0
  36. Master_SSL_Verify_Server_Cert:No
  37. Last_IO_Errno:0
  38. Last_IO_Error:
  39. Last_SQL_Errno:0
  40. Last_SQL_Error:
  41. Replicate_Ignore_Server_Ids:
  42. Master_Server_Id:1
  43. 1rowinset(0.00sec)

好了,可以看到Slave_IO_Running和Slave_SQL_Running都是yes了,下面也没有error提示了.

4.验证,在mysql主上创建个数据库.

mysql> create database emlog;

Query OK, 1 row affected (0.01 sec)

再在mysql从上查看是否有这个数据库,代码如下:

  1. mysql>showdatabases;
  2. +--------------------+
  3. |Database|
  4. +--------------------+
  5. |information_schema|
  6. |emlog|
  7. |mysql|
  8. |performance_schema|
  9. |test|
  10. +--------------------+
  11. 5rowsinset(0.02sec)

可以看到已经同步复制过来了,mysql主从搭建成功.

相关广告
  • mysql 5.5数据库主从配置步骤详解 mysql 5.5数据库主从配置步骤详解 mysql 5.5数据库主从配置步骤详解
相关阅读

mysql 5.5数据库主从配置步骤详解

2019/10/10 17:36:10 | 谷歌SEO算法 | 区块链