Linux安装配置MySQL

MySQL下载

• MySQL官网下载地址

环境信息

• MySQL : 5.5.17-1.el6.x86_64.rpm
• Linux : Red Hat Enterprise Linux Server release 6.5 (Santiago)

安装

检查是否已安装

rpm -qa|grep -i mysql

如返回:

mysql-libs-5.1.71-1.el6.x86_64

表示已安装库文件，需先卸载，卸载命令如下:

rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps

注意需添加 –nodeps 参数忽略依赖，否则会出错

配置用户及用户组

groupadd mysql
useradd -r -g mysql mysql

安装服务端软件

rpm -ivh MySQL-server-5.5.17-1.el6.x86_64.rpm

注意切换到root用户
输出如下

Preparing...                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h TMCT-127.152 password 'new-password'

Alternatively you can run:
/usr/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.

Please report any problems with the /usr/bin/mysqlbug script!

启动MySQL服务

service mysql start

查看MySQL是否启动成功

netstat -anp|grep 3306

输出:

tcp   0   0 0.0.0.0:3306    0.0.0.0:*     LISTEN

安装MySQL客户端软件

rpm -ivh MySQL-client-5.5.17-1.el6.x86_64.rpm

配置MySQL

执行mysql_secure_installation

sh /usr/bin/mysql_secure_installation

mysql_secure_installation主要执行配置了如下几件事:

• 为root用户设置密码
• 删除匿名账号
• 取消root用户远程登录
• 删除test库和对test库的访问权限
• 刷新授权表使修改生效

登录mysql

mysql -uroot -p -h127.0.0.1

执行mysql_secure_installation后需指定host为127.0.0.1

创建用户及授权

create user 'username'@'%' identified by 'password';
grant all privileges on *.* to 'username'@'%' with grant option;

privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等

配置双实例

拷贝现有数据库文件

cp -r /var/lib/mysql /var/lib/mysql_3307

给数据库文件赋权限

chown -R mysql.mysql /var/lib/mysql_3307

创建一个新的空数据库

mysql_install_db --datadir=/var/lib/mysql_3307 --user=mysql

创建multi的配置cnf文件

touch /usr/local/my_multi.cnf

内容如下

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = root　　　　#用于登陆和关闭此服务
password   = 123456　　　#同上

[mysqld3307]
socket     = /tmp/mysql_3307.sock
port       = 3307
pid-file   = /var/lib/mysql_3307/3307.pid
datadir    = /var/lib/mysql_3307/
log        = /var/lib/mysql_3307/3307.log
character-set-server    = utf8
user       = mysql

启动你的多实例

mysqld_multi --defaults-extra-file=/usr/local/my_multi.cnf start 3307

检查是否启动成功

netstat -ntlp

登录新实例

mysql -uroot -S /tmp/mysql_3307.sock -p

再次确认

show variables like '%datadir%';

配置主从实例

my_multi.cnf配置

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /opt/log/mysql/mysqld_multi.log


[mysqld3306]
socket     = /tmp/mysql_3306.sock
port       = 3306
pid-file   = /var/lib/mysql/3306.pid
datadir    = /var/lib/mysql/
log        = /var/lib/mysql/3306.log
character-set-server    = utf8
user       = mysql
server-id  = 9306
log-bin    = /var/lib/mysql/binlog
log-bin-index = /var/lib/mysql/binlog.index
binlog-ignore-db="mysql,test"

[mysqld3307]
socket     = /tmp/mysql_3307.sock
port       = 3307
pid-file   = /var/lib/mysql_3307/3307.pid
datadir    = /var/lib/mysql_3307/
log        = /var/lib/mysql_3307/3307.log
character-set-server    = utf8
user       = mysql
server-id  = 9307
relay-log  = /var/lib/mysql_3307/relay_bin
relay-log-index = /var/lib/mysql_3307/relay_bin.index
log_slave_updates = 1
read_only  = 1

master需指定log-bin、log-bin-index
slave需指定relay-log、relay-log-index
文件权限需指定为mysql.mysql
master，slave的service-id不能相同
mysqld组后只能跟数字

启动命令:

mysqld_multi --defaults-extra-file=./my_multi.cnf start

登录主库 创建复制用户

grant replication slave, replication client on *.* to repl@'localhost' identified by 'repl';
flush privileges;

登录主库查看主备日志

show master logs;

结果如下

+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       107 |
+---------------+-----------+

登录从库

reset slave;

change master to master_host='localhost', MASTER_PORT=3306, master_user='repl', master_password='repl', master_log_file='binlog.000001', master_log_pos=107;

start slave; 

master_log_file：master日志文件
master_log_pos：master日志文件开始同步位置