mysql安装
-
安装依赖库
libaio-0.3.109-13.el7.x86_64.rpm
libao-1.1.0-8.el7.x86_64.rpmwget http://mirror.centos.org/centos/7/os/x86_64/Packages/libaio-devel-0.3.109-13.el7.x86_64.rpm wget http://mirror.centos.org/centos/7/os/x86_64/Packages/libao-1.1.0-8.el7.x86_64.rpm rpm -ivh libaio-devel-0.3.109-13.el7.x86_64.rpm rpm -ivh libao-1.1.0-8.el7.x86_64.rpm
-
下载mysql,并解压: 官网:https://dev.mysql.com/downloads/mysql/
# 解压 tar mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz # 移动并改名 mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql
-
添加用户和组,进入
/usr/local
目录下groupadd mysql useradd -r -g mysql mysql
-
创建
mysql
的data
目录mkdir /usr/local/mysql/data/
-
新建
my.cnf
文件 路径为/etc/my.cnf
vim /etc/my.cnf
my.cnf文件内容如下:
[client] port = 3306 socket = /tmp/mysql.sock [mysqld] init-connect='SET NAMES utf8' basedir=/usr/local/mysql datadir=/usr/local/mysql/data socket=/tmp/mysql.sock max_connections=50 character-set-server=utf8 default-storage-engine=INNODB
-
配置
mysql
环境变量# 修改权限 chmod +x /usr/local/mysql/bin/* # 编辑配置文件 vim /etc/profile # 配置环境变量 export PATH=$PATH:/usr/local/mysql/bin export PATH=$PATH:/usr/local/mysql/support-files # 使配置文件生效 source /etc/profile
-
初始化
mysql
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
-
修改文件夹权限
chown -R root:root /usr/local/mysql/ chown -R mysql:mysql /usr/local/mysql/data/
-
启动
mysql
mysql.server start
-
修改密码
mysql -uroot use mysql; update user set authentication_string=password('密码') where user='root'; flush privileges; exit;
-
设置外网访问
update user set host='%' where user ='root'; flush privileges;
-
centos7
打开远程连接防火墙firewall-cmd --permanent --add-port=3306/tcp service firewalld restart
主从库配置
1.环境:CentOS7.3
2. 主库:mysql 5.6.50 ip:192.168.75.131:3306
3. 从库:mysql 5.6.50 ip:192.168.75.132:3306
主从配置步骤:
1.修改主库的mysql下的my.cnf文件,该文件在/etc目录下:
vi my.cnf
2.修改my.cnf,在[mysqld]增加如下修改
server-id=1
log-bin=mysql-bin
log-slave-updates=1
binlog-do-db=repl #需要同步的数据库,如果没有本行表示同步所有的数据库
binlog-ignore-db=mysql #被忽略的数据
3.随后以root身份登入mysql:
mysql -u root -p;
4.创建一个用于从库的用户:
grant replication slave on *.* to 'master'@'192.168.75.132' identified by 'syl199746';
flush privileges;
5.重启mysql服务:
mysql.server restart
6.重启后记录下主库的binlog文件的位置:
show master status;
7.修改从库的my.cnf文件,在[mysqld]下增加:
server-id=2
log-bin= mysql-bin
relay-log= mysql-relay-bin
read-only=1 #设置为只读
log-slave-updates=1 #实现级联复制需要,即把relay-log中的同步到bin-log中
replicate-do-db=repl #要同步的数据库,不写本行表示同步所有数据库
8.重启mysql服务; mysql.server restart
9.从库设置slave:
CHANGE MASTER TO
MASTER_HOST='192.168.75.131',
MASTER_USER='master',
MASTER_PASSWORD='syl199746',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=120;
10.启动slave; start slave
11.查看slave状态:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.211.153
Master_User: nefslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 14056
Relay_Log_File: beta-5gca-1-relay-bin.000010
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
观察到Slave_IO_Running,Slave_SQL_Running都为YES即为启动成功
如果需要配置连接mycat,需要进入mysql增大最大连接数:set GLOBAL max_connections=256;
不然后面会出现too many connection的情形。
如果需要配置互为主从,只要重复上述步骤,将IP互调一下即可
MySQL互为主从
创建用户及权限管理
create user supermarketlinker identified by 'password';
grant all privileges on litemall.* to supermarketlinker@'%' identified by 'password';
flush PRIVILEGES;