mysql 5.5数据库主从配置步骤介绍
发布时间：2022-06-20 13:18:03 所属栏目：MySql教程 来源：互联网
导读：上次给大家介绍了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.安装
|
上次给大家介绍了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.安装前准备 wget http://mysql.llarian.net/Downloads/MySQL-5.5/mysql-5.5.33.tar.gz yum -y install gcc gcc-c++ libtool-libs autoconf freetype-devel gd libjpeg-devel libpng-devel libxml2-devel ncurses-devel zlib-devel zip unzip curl-devel wget crontabs file bison cmake patch mlocate flex diffutils automake make kernel-devel cpp readline-devel openssl-devel vim-minimal glibc-devel glib2-devel bzip2-devel e2fsprogs-devel libidn-devel gettext-devel expat-devel libcap-devel libtool-ltdl-devel pam-devel pcre-devel libmcrypt-devel 2.在服务器a上安装mysql tar zxf mysql-5.5.33.tar.gz && cd mysql-5.5.33 /usr/sbin/groupadd mysql /usr/sbin/useradd -g mysql mysql 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 make make install chmod +w /usr/local/mysql chown -R mysql:mysql /usr/local/mysql cd support-files/ cp my-medium.cnf /etc/my.cnf cp -f mysql.server /etc/rc.d/init.d/mysqld mkdir /var/lib/mysql /usr/local/mysql/scripts/mysql_install_db --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/var/lib/mysql --user=mysql chmod +x /etc/rc.d/init.d/mysqld chkconfig --add mysqld chkconfig mysqld on cat > /etc/ld.so.conf.d/mysql.conf<<EOF /usr/local/mysql/lib/mysql /usr/local/lib EOF ldconfig 如果你是在64位机器上的话,那你就执行下面这个命令: ln -s /usr/local/mysql/lib/mysql /usr/lib64/mysql 如果你是在32位机器上的话,那就执行下面的命令: ln -s /usr/local/mysql/lib/mysql /usr/lib/mysql 然后再执行: ln -s /usr/local/mysql/bin/mysql /usr/bin ln -s /usr/local/mysql/bin/mysqladmin /usr/bin ln -s /usr/local/mysql/bin/mysqldump /usr/bin service mysqld start mysqladmin -u root password 'password' 当然如果大家觉得编译安装mysql很麻烦的话,那么可以去看看这篇文章安装mysql5.5二进制包,服务器b安装mysql配置跟服务器a一样,这里就不说了. 3.配置主从,在服务器a上: vi /etc/my.cnf [mysqld] log-bin=master-bin log-bin-index=master-bin.index server-id = 1 innodb_file_per_table = 1 binlog_format=mixed 授权复制用户: mysql -u root -p grant replication slave on *.* to 'dbmysql'@'%' identified by '123456'; flush privileges; 重启mysql:service mysqld restart 在服务器b上: vi /etc/my.cnf [mysqld] relay-log = relay-log relay-log-index = relay-log.index server-id = 2 innodb_file_per_table = 1 binlog_format=mixed 然后重启mysql:service mysqld restart 服务器b连接连接主服务器并复制,先在服务器a上查看master的状态: mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000001 | 107 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 然后在服务器b上进行连接: mysql> change master to master_host='192.168.10.151',master_user='dbmysql',maste r_password='123456',master_log_file='master-bin.000001',master_log_pos=107; 查看一下slave状态,代码如下: mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.10.151 Master_User: dbmysql Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: localhost-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: 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: 107 Relay_Log_Space: 107 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: --phpfensi.com Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) 启动slave再查看: mysql> start slave; Query OK, 0 rows affected (0.01 sec) mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.10.151 Master_User: dbmysql Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: localhost-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 107 Relay_Log_Space: 107 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'dbmysql@192.168.10.151:3306' - retry-time: 60 retries: 86400 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) 看到上面错误了没,说明mysql主和从服务器的防火墙没有开放3306端口,去服务器a和服务器b上把3306端口打开,再来查看mysql从的状态,代码如下: mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.151 Master_User: dbmysql Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 107 Relay_Log_File: relay-log.000011 Relay_Log_Pos: 254 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 107 Relay_Log_Space: 404 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) 好了,可以看到Slave_IO_Running和Slave_SQL_Running都是yes了,下面也没有error提示了. 4.验证,在mysql主上创建个数据库. mysql> create database emlog; Query OK, 1 row affected (0.01 sec) 再在mysql从上查看是否有这个数据库,代码如下: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | emlog | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.02 sec) 可以看到已经同步复制过来了,mysql主从搭建成功. （编辑：开发网_开封站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐
热点阅读
浙公网安备 33038102330459号