MySQL主从配置

一、MySQL主从原理 MySQL 复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。每个从服务器从主服务器接收主服务器已经记录到其二进制日志的保存的更新,以便从服务器可以对其数据拷贝执行相同的更新。

将主服务器的数据拷贝到从服务器的一个途径是使用LOAD DATA FROM MASTER语句。请注意LOAD DATA FROM MASTER目前只在所有表使用MyISAM存储引擎的主服务器上工作。并且,该语句将获得全局读锁定。

MySQL 使用3个线程来执行复制功能,其中1个在主服务器上,另两个在从服务器上。当发出START SLAVE时,从服务器创建一个I/O线程,以连接主服务器并让它发送记录在其二进制日志中的语句。 主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以识别为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。 从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中,即中继日志。
第3个线程是SQL线程,是从服务器创建用于读取中继日志并执行日志中包含的更新。 有多个从服务器的主服务器创建为每个当前连接的从服务器创建一个线程;每个从服务器有自己的I/O和SQL线程。 二、MySQL主从的准备和实验环境 操作系统CentOS release 6.5 (Final)内核版本:2.6.32-431.el6.x86_64 实验需要的软件版本:mysql-5.5.32 主服务器IP:192.168.0.151从服务器IP:192.168.0.152 三、配置MySQL主从步骤 3.1.1),准备安装前所需要的软件及其依赖包: [root@contrlhost ~]#yum -y install gcc gcc-c++ make ncurses ncurses-devel libaio-devel cmake [root@contrlhost ~]#rpm -qa ncurses-devel libaio-devel cmake 3.1.2),创建用户和组,创建安装目录 [root@contrlhost ~]#groupadd mysql [root@contrlhost ~]# useradd mysql -s/sbin/nologin -M -g mysql [root@contrlhost ~]#id mysql [root@contrlhost ~]#cd ~ [root@contrlhost ~]#mkdir /application 3.1.3),上传mysql-5.5.32软件到主机上 [root@contrlhost ~]#rz 3.1.4),解压mysql-5.5.32 [root@contrlhost ~]#tar xvf mysql-5.5.32.tar.gz 3.1.5),编译安装mysql-5.5.32 [root@contrlhost ~]#cd mysql-5.5.32 [root@contrlhost mysql-5.5.32]#cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32
-DMYSQL_DATADIR=/data/mysql
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii
-DENABLED_LOCAL_INFILE=ON
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1
-DWITH_FAST_MUTEXES=1
-DWITH_ZLIB=bundled
-DENABLED_LOCAL_INFILE=1
-DWITH_READLINE=1
-DWITH_EMBEDDED_SERVER=1
-DWITH_DEBUG=0 [root@contrlhost mysql-5.5.32]#make && make install 3.1.7),设置软链接并给其授权 [root@contrlhost mysql-5.5.32]#ln -s /application/mysql-5.5.32/ /application/mysql [root@contrlhost mysql-5.5.32]#ls -l /application/ [root@contrlhost mysql-5.5.32]#ls /application/mysql/ [root@contrlhost mysql-5.5.32]#cp support-files/mysql.server /etc/init.d/mysqld [root@contrlhost mysql-5.5.32]#chmod 700 /etc/init.d/mysqld 3.1.7),配置环境变量 [root@contrlhost mysql-5.5.32]#ls /application/mysql/bin/mysql [root@contrlhost mysql-5.5.32]#echo ' export PATH=/application/mysql/bin:$PATH '>>/etc/profile [root@contrlhost mysql-5.5.32]#source /etc/profile [root@contrlhost mysql-5.5.32]#tail -n 1 /etc/profile [root@contrlhost mysql-5.5.32]#echo $PATH 四、MySQL主上配置my.cnf [root@contrlhost mysql-5.5.32]#ll support-files/.cnf [root@contrlhost mysql]# ll support-files/.cnf -rw-r--r-- 1 root root 4721 Dec 11 14:33 support-files/my-huge.cnf -rw-r--r-- 1 root root 19809 Dec 11 14:33 support-files/my-innodb-heavy-4G.cnf -rw-r--r-- 1 root root 4695 Dec 11 14:33 support-files/my-large.cnf -rw-r--r-- 1 root root 4706 Dec 11 14:33 support-files/my-medium.cnf -rw-r--r-- 1 root root 2870 Dec 11 14:33 support-files/my-small.cnf [root@contrlhost mysql-5.5.32]#mv /etc/my.cnf /etc/my.cnf.bak 在此可以自定义配置my.cnf [root@contrlhost mysql]# cat /etc/my.cnf [mysqld] port = 3306 socket = /application/mysql-5.5.32/tmp/mysql.sock datadir =/data/mysql user =mysql #Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 log-bin=mysql-bin server-id = 1 auto_increment_offset=1 auto_increment_increment=2 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid replicate-do-db =all 初始化MySQL,此步骤关键一步。 [root@contrlhost scripts]# /application/mysql/scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/mysql --user=mysql WARNING: The host 'contrlhost' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL daemon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing MySQL system tables... OK Filling help tables... OK To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: /application/mysql//bin/mysqladmin -u root password 'new-password' /application/mysql//bin/mysqladmin -u root -h contrlhost password 'new-password'

Alternatively you can run: /application/mysql//bin/mysql_secure_installation

which will also give you the option of removing the test databases and anonymous user created by default. This is strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with: cd /application/mysql/ ; /application/mysql//bin/mysqld_safe & You can test the MySQL daemon with mysql-test-run.pl cd /application/mysql//mysql-test ; perl mysql-test-run.pl Please report any problems with the /application/mysql//scripts/mysqlbug script! [root@contrlhost mysql-5.5.32]#/etc/init.d/mysqld start [root@contrlhost mysql-5.5.32]#chkconfig --add mysqld [root@contrlhost mysql-5.5.32]#chkconfig --list mysqld 启动服务登录MySQL: [root@contrlhost scripts]# /etc/init.d/mysqld start Starting MySQL....... SUCCESS! [root@contrlhost scripts]##mysql 修改登录MySQL密码: [root@contrlhost scripts]# mysqladmin -uroot password 'hwg123' 登录数据库: [root@contrlhost scripts]# mysql -uroot -phwg123 mysql> select user,host,password from mysql.user; +------+------------+-------------------------------------------+ | user | host | password | +------+------------+-------------------------------------------+ | root | localhost | *306D1D5B0F3CE16BD454D359127EE7292A69A8E7 | | root | contrlhost | | | root | 127.0.0.1 | | | root | ::1 | | | | localhost | | | | contrlhost | | +------+------------+-------------------------------------------+ 6 rows in set (0.04 sec) 删除空用户名;contrlhost; ::1 等等; mysql> delete from mysql.user where user=''; mysql> delete from mysql.user where host='::1'; 如果有其他的IP不是你要授权的IP就把它删除; mysql> delete from mysql.user where host='192.168.0.151'; mysql> delete from mysql.user where host='contrlhost'; mysql> select user,host from mysql.user;
在Master上设置授权给slave用户 mysql> grant all privileges on . to 'rep'@'192.168.0.%'identified by 'hwg123' with grant option; mysql> flush privileges; 查询授权用户信息 mysql> select user,host,password from mysql.user; +------+-------------+-------------------------------------------+ | user | host | password | +------+-------------+-------------------------------------------+ | root | localhost | *306D1D5B0F3CE16BD454D359127EE7292A69A8E7 | | root | 127.0.0.1 | | | rep | 192.168.0.% | *306D1D5B0F3CE16BD454D359127EE7292A69A8E7 | +------+-------------+-------------------------------------------+ 3 rows in set (0.00 sec) 接着确定Master上的bin-log文件和Post点位: mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 250 | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 五、在Slave上执行操作 上述第三大步骤后此处略过! 修改登录MySQL密码: [root@glpi ~]# mysqladmin -uroot password 'hwg123' mysql> change master to master_host='192.168.0.151',master_user='rep',master_password='hwg123',master_port=3306,master_log_file='mysqlbin.000003',master_log_pos=2164; 查看mysql slave正常与否; mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.151 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 972 Relay_Log_File: glpi-relay-bin.000002 Relay_Log_Pos: 1118 Relay_Master_Log_File: mysql-bin.000004 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: 972 Relay_Log_Space: 1273 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.07 sec) 关键要看看IO和mysql两个进程是否正常;一般是两个OK即可;如果不正常需要看看具体的日志文件 错误的情况: 1,Slave_IO_Running: No 显示为NO Slave_IO_Running: No Slave_SQL_Running: Yes 先查看日志: [root@glpi ~]# tail -fn 100 /var/log/mysqld.log 解决办法: 1,重启master库:/etc/init.d/mysqld restart 2,mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 972 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.03 sec) 3,停止slave同步: mysql> slave stop; 4,根据mysql-bin和Master_Log_Pos重新设定; mysql> change master to Master_Log_File='mysql-bin.000004',Master_Log_Pos=972; mysql> slave start; mysql> show slave status\G

本站公众号
   欢迎关注本站公众号,获取更多程序园信息
开发小院