MySQL负载均衡配置

最新推荐文章于 2024-07-11 16:26:04 发布

Klein_yang

最新推荐文章于 2024-07-11 16:26:04 发布

阅读量758

点赞数

分类专栏： mysql 文章标签： mysql 数据库 centos

本文链接：https://blog.csdn.net/Klein_yang/article/details/78308973

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MySQL负载均衡配置

本文从centos刚装完系统开始，描述了包括数据库mysql安装及配置，数据库主从配置等操作步骤及相关代码。本文介绍MySQL5.5的安装配置

系统：CentOS 6.5 x64
MySQL版本：MySQL5.5

安装MySQL

centos会默认安装MySQL5.1版本，我们需要的是MySQL5.5版本，所以首先需要将默认安装的MySQL版本删除。
第一步就是看linu是否安装了mysql

rpm -qa|grep mysql

查看到centos下安装了mysql5.1，那就开始卸载
接下来就是卸载mysql5.1了

rpm -e mysql-libs --nodeps

yum中之后mysql5.1，安装还是5.1，现在就要去增加一个新的repo

rpm -Uvh http://mirror.steadfast.net/epel/6/i386/epel-release-6-8.noarch.rpm

一共需要增加两个repo，现在增加另一个

rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

现在查看一些是否增加成功了

yum --enablerepo=remi,remi-test list mysql mysql-server

看到下面这些就是安装成功了。
接下来就是安装mysql5.5了

yum --enablerepo=remi,remi-test install mysql mysql-server

接下来需要启动一下mysql

/etc/init.d/mysqld start

下面这条命令设置开机自启动，省的我们每次开机就去启动一下mysql了。

chkconfig --levels 345 mysqld on

要启用MySQL 安全设置请输入以下命令

/usr/bin/mysql_secure_installation
[root@server1 ~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none):<–初次运行直接回车
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] <– 是否删除匿名用户,生产环境建议删除，所以直接回车
… Success!
Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] <–是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
… Success!
By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] <– 是否删除test数据库,直接回车
- Dropping test database…
… Success!
- Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] <– 是否重新加载权限表，直接回车
… Success!
Cleaning up…
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
[root@server1 ~]#

mysql安装成功
在本机先使用root用户登录mysql：

mysql -u root -p"youpassword"

进行授权操作：

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'youpassword' WITH GRANT OPTION;

重载授权表：

FLUSH PRIVILEGES;

退出mysql数据库：

exit

添加防火墙策略

[root@admin nginx-1.9.9]# /sbin/iptables -I INPUT -p tcp --dport 3389 -j ACCEPT
[root@admin nginx-1.9.9]# /etc/init.d/iptables save

完成mysql配置

数据库迁移

修改MySQL的配置，将文件中的/var/lib/全部替换成新路径

vim /usr/lib64/mysql/mysql_config
vim /usr/bin/mysqld_safe 
vim /etc/init.d/mysqld 
vim /etc/my.cnf

重启后若出现

[root@www ~]# service mysqld restart
mysqld stopped:                                            [  OK  ]
MySQL Daemon failed to start.
mysqld started:                                            [FAILED]
[root@www ~]#

可执行

install -m 0700 -o mysql -g mysql -d /var/lib/mysql-files
service mysqld start

数据库主从配置

修改主MySQL的配置

[root@localhost etc] vi /etc/my.cnf

添加以下配置

server-id=1
log-bin=mysql-bin #这个一定得设置，否则没有日志的话，从数据库上会报错

创建主从复制的帐号：

mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'192.168.137.197' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

如果该命令无法执行，请检查是否用了圆角的’，需要用半角的’，再不行，那就是mysql数据库安装不成功。

192.168.137.197是从库的地址
backup是需要同步的用户名
123456为远程同步密码

解锁表

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

正确的状态如下：

mysql> SHOW MASTER STATUS;

配置错误的情况，解决办法见文章最后的疑难解答。

mysql> SHOW MASTER STATUS;
Empty set (0.00 sec)

修改从库配置：

[root@localhost ~]# vi /etc/my.cnf
#添加语句
log-bin = mysql-bin
server_id = 2

mysql> CHANGE MASTER TO
     MASTER_HOST='192.168.137.33',
     MASTER_USER='backup',
     MASTER_PASSWORD='123456',
     MASTER_PORT=3306,
     MASTER_LOG_FILE='mysql-bin.000001',
     MASTER_LOG_POS=106,
     MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.02 sec)
#测试主从是否配置成功。
mysql> show slave status\G

数据库测试：
（1）主从都新建了test数据库和test数据表：

CREATE TABLE `test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8；

（2）往主库插入数据行：

INSERT into test(name,update_time) value ('helo','2015-10-27 19:09:00');

恭喜，你会发现数据已经得到同步。

总结：
主从复制完成，但是还不能满足我们的需求。这种配置方式只是将主库的数据同步到从库。
我们设想下：有一天我们往从库插入或者删除数据，那么主库的数据不会得到改变。就会造成数据不一致。
下一章将配置主从数据互为同步。

疑难解答：
1、

mysql> SHOW MASTER STATUS;
Empty set (0.00 sec)

主库出现上面的提示说明my.cnf配置不正确。
正常的应该是如下：

mysql> SHOW MASTER STATUS;

2、此外，执行：

mysql> show slave status\G;

如果出现这样子的错误：

Slave_IO_Running: No
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server id was not set'
或者：
Slave_IO_Running: No
Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
正确的应该是：
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

你会疑惑，不是已经设置了server-id了吗?
如果在网上有人告诉你使用指令：mysql> set global sql_slave_skip_counter=1;
你可以直接问候他全家。
造成该错误的原因是：server-id没有列在[mysqld]下面：
正确的my.cnf如下：

[mysqld]
log-bin = mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server_id = 2
master-host = 192.168.137.33
master-user = backup
master-pass = 123456
master-port = 3306
master-connect-retry = 60
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Klein_yang

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL负载均衡配置

MySQL负载均衡配置本文从centos刚装完系统开始，描述了包括数据库mysql安装及配置，数据库主从配置等操作步骤及相关代码。本文介绍MySQL5.5的安装配置系统：CentOS 6.5 x64MySQL版本：MySQL5.5安装MySQLcentos会默认安装MySQL5.1版本，我们需要的是MySQL5.5版本，所以首先需要将默认安装的MySQL版本删除。第一...
复制链接

扫一扫