CentOS 7.2 安装配置mysql主从服务器
MySQL官方压缩包安装:
1:下载mysql官方版本,此处以目前最新版本5.7.14为例,下载的64位版本文件为:
mysql-5.7.14-linux-glibc2.5-x86_64.tar
2:解压文件
mv mysql-5.7.14-linux-glibc2.5-x86_64.tar /opt/mysql-5.7.14-linux-glibc2.5-x86_64.tar tar -zvxf mysql-5.7.14-linux-glibc2.5-x86_64.tar.gz mv mysql-5.7.14-linux-glibc2.5-x86_64 /data1/
mv mysql-5.7.14-linux-glibc2.5-x86_64 mysql
这里/data1是服务器上的挂载扩展点,因此放在该挂载点下,并将解压文件夹改名为mysql
3:创建mysql用户及用户组
groupadd mysql
useradd -r -g mysql mysql
这里-r表示该用户为系统用户,不能用于登录,可通过以下查看组及用户信息:
cat /etc/group | grep mysql cat /etc/passwd | grep mysql
4:变更mysql文件夹所属用户及组
chown -R mysql ./mysql chgrp -R mysql ./mysql
5:mysql初始化
cd /data1/mysql
./bin/mysql_install_db --user=mysql --basedir=/data1/mysql --datadir=/data1/mysql/data/
正常情况下会出现如下结果:
2016-09-05 16:09:33 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize 2016-09-05 16:09:35 [WARNING] The bootstrap log isn't empty: 2016-09-05 16:09:35 [WARNING] 2016-09-05T08:09:33.109112Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead 2016-09-05T08:09:33.109668Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2016-09-05T08:09:33.109675Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
6:编辑启动及配置文件
vim /data1/mysql/support-files/my-default.cnf
将配置文件内容按照如下修改:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[client] default-character-set=gbk [mysqld] character-set-server=gbk max_allowed_packet=60MB max_connections=2000 datadir=/data1/mysql socket=/data1/mysql/mysql.sock symbolic-links=0 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysql] default-character-set=gbk socket=/data1/mysql/mysql.sock [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
也可以将log及pid文件都改为/data1目录(有人反映会遇到/var/run权限不足等问题)。
vim /data1/mysql/support-files/mysql.server
启动文件内修改basedir及datadir内容:
basedir=/data1/mysql
datadir=/data1/mysql/data
同时,为了在首次启动后有权限登录mysql,先在启动指令(start)中取消权限验证:
case "$mode" in 'start') # Start daemon # Safeguard (relative paths, core dumps..) cd $basedir echo $echo_n "Starting MySQL" if test -x $bindir/mysqld_safe then # Give extra arguments to mysqld with the my.cnf file. This script # may be overwritten at next upgrade. $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args --skip-grant-tables >/dev/null 2>&1 & wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$? # Make lock for RedHat / SuSE if test -w "$lockdir" then touch "$lock_file_path" fi exit $return_value else log_failure_msg "Couldn't find MySQL server ($bindir/mysqld_safe)" fi ;;
在mysqld_safe启动指令的参数中,暂时加入“--skip-grant-tables”参数,这一步非常重要,否则会报各种类似错误:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES). ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO).
即使正确地用mysql临时生成的密码(/root/.mysql_secret)也是如此。
然后将配置文件、启动文件copy至目的地:
cp -af ./my-default.cnv /etc/my.cnf cp -af ./mysql.server /etc/init.d/mysqld
7:配置mysql的环境变量
vim /etc/profile
在末尾添加:
export PATH=$PATH:/data1/mysql/bin
生效环境变量:
source /etc/profile
8:配置mysql启动服务
chkconfig --add mysqld chkconfig --level 35 mysql on chkconfig
正常情况下mysqld已设置为系统服务:
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off netconsole 0:off 1:off 2:off 3:off 4:off 5:off 6:off network 0:off 1:off 2:on 3:on 4:on 5:on 6:off
9:创建自定义的日志目录(可选)
如果之前配置文件中日志目录修改为/data1/mysql/log,则需要手动创建该目录:
mkdir /data1/mysql/log chown -R mysql /data1/mysql/log chgrp -R mysql /data1/mysql/log
10:启动mysql服务
在centOS7中,直接用systemctl启动即可:
systemctl start mysql
11:更改root用户密码并分配远程访问权限
由于之前在start中取消了权限验证,所以现在可以不输入密码直接连上数据库,这里我们先更改root用户密码,并分配远程访问权限,成功后将去除start启动指令中的取消权限验证参数。
mysql -u root mysql
无需验证直接连接mysql,成功后出现mysql>提示符:
mysql> use mysql mysql> update user set authentication_string=password('123456') where user='root'; mysql> flush privileges; mysql> grant all on *.* to root@'%' identified by '123456';
第一行切换当前数据库;
第二行修改root账户密码,这里的“authentication_string”是较新的版本,老版本的密码字段是"password";
第三行刷新权限;
第四行分配远程访问权限给root用户,这里的密码是“123456”。
接下来修改/etc/rc.d/init.d/mysqld文件,去除脚本中start部分的"--skip-grant-tables"参数,并重启mysql服务,取消匿名登录
'start') # Start daemon # Safeguard (relative paths, core dumps..) cd $basedir echo $echo_n "Starting MySQL" if test -x $bindir/mysqld_safe then # Give extra arguments to mysqld with the my.cnf file. This script # may be overwritten at next upgrade. $bindir/mysqld_safe --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args>/dev/null 2>&1 & wait_for_pid created "$!" "$mysqld_pid_file_path"; return_value=$? # Make lock for RedHat / SuSE if test -w "$lockdir" then touch "$lock_file_path" fi exit $return_value else log_failure_msg "Couldn't find MySQL server ($bindir/mysqld_safe)" fi ;;
注意 mysqld_safe 启动指令后的 --skip-grant-tables 参数已经删除。
刷新服务文件并重启mysql服务:
systemctl daemon-reload
systemctl restart mysql
完成mysql的安装,并且已经可以通过root用户用指定的密码'123456'进行远程访问。
注:如果开启了防火墙,则可通过开启3306端口,或开启mysql服务的方式来配置firewalld:
firewall-cmd --zone=public --add-port=3306/tcp --permanent firewall-cmd --zone=public --add-service=mysql --permanent firewall-cmd --reload
MySQL主从复制配置:
目标:两台mysql服务器互为主从,达到数据互备的目的。
1:修改mysql配置文件
服务器A配置文件(/etc/my.cnf),在mysqld标签下新增如下内容:
log-bin=/data1/mysql/binlog binlog_format=mixed server-id=153 slave-skip-errors=1062,1051,1146,1032 read-only=0 binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=mysql auto-increment-increment=10 auto-increment-offset=1 replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=mysql relay_log=/data1/mysql/relaylog log-slave-updates=ON
服务器B配置文件(/etc/my.conf),在mysql标签下新增如下内容:
log-bin=/data1/mysql/binlog binlog_format=mixed server-id=154 slave-skip-errors=1062,1051,1146,1032 read-only=0 binlog-ignore-db=information_schema binlog-ignore-db=performance_schema binlog-ignore-db=mysql auto-increment-increment=10 auto-increment-offset=2 replicate-ignore-db=information_schema replicate-ignore-db=performance_schema replicate-ignore-db=mysql relay_log=/data1/mysql/relaylog log-slave-updates=ON
两台服务器的区别在server-id(可用IP最后一节),以及auto-increment-offset上,自增间隔10,offset分别为1,2可保证对不同服务器并发写操作的id不会冲突。
2:mysql服务器备份用户创建及授权
以命令行模式连接服务器A,创建用户repl并授权:
mysql> create user 'repl'@'server B IP address' identified by '123456'; mysql> grant replication slave on *.* to 'repl'@'server B IP address' identified by '123456'; mysql> flush privileges;
以命令行模式连接服务器B,创建用户repl并授权:
mysql> create user 'repl'@'server A IP address' identified by '123456'; mysql> grant replication slave on *.* to 'repl'@'server A IP address' identified by '123456'; mysql> flush privileges;
3:重启mysql服务器并设置双机互备
在两台mysql服务器上分别重启mysql服务:
systemctl restart mysql
在mysql服务器A上查看master信息:
mysql> show master status;
一般会看到如下信息:
+---------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+---------------------------------------------+-------------------+ | binlog.000001 | 329 | | information_schema,performance_schema,mysql | | +---------------+----------+--------------+---------------------------------------------+-------------------+ 1 row in set (0.00 sec)
此时连上服务器B的mysql客户端,配置主服务器:
change master to master_host='server A IP address', master_user='repl', master_password='123456', master_log_file='binlog.000001', master_log_pos=329; start slave;
同理,连接上mysql服务器B的mysql控制台,执行如下指令:
mysql> show master status;
获取服务器B的master信息,类似如下:
+---------------+----------+--------------+---------------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+---------------------------------------------+-------------------+ | binlog.000002 | 329 | | information_schema,performance_schema,mysql | | +---------------+----------+--------------+---------------------------------------------+-------------------+ 1 row in set (0.00 sec)
此时连上服务器A的mysql客户端,配置主服务器:
change master to master_host='server B IP address', master_user='repl', master_password='123456', master_log_file='binlog.000002', master_log_pos=329; start slave;
到现在,mysql服务器A与服务器B之间已正确配置了双机主从互备,可以通过show slave status指令查看,其中的Slave_IO_Running是否为Connecting,Slave_SQL_Running是否为Yes。
也可以在服务器A上对数据库做出变更,比如create table操作,会在短时间内自动同步至服务器B。
2016-10-9更新:
在mysql控制台执行change master指令有可能由于之前已配置过主从复制而导致失败,此时需要先停止主从复制的相关READ操作:
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
之后再执行change master指令即可。同时要注意在变更主从复制配置之前,请先确认主从复制用户的权限及密码正确性,并重启mysql。
同时,mysql的错误日志会提供大量信息。
2016-10-18更新:
在mysql配置文件(默认路径/etc/my.cnf)中,需要通过配置来开启函数脚本执行,同时该配置项也会影响主从复制,如下为典型的错误提示:
Error Code: 1418 This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
方案1:通过设置全局变量来开启,但重启mysqld后会失效
set global log_bin_trust_function_creators=TRUE
方案2:通过修改mysql配置文件(默认路径/etc/my.cnf),在mysqld配置节中加入配置信息
log_bin_trust_function_creators=true
重启mysqld守护进程即可。