MySQL安装、配置及主从部署（CentOS 7.6）

MySQL安装-A.rpm方式

MySQL rpm包官方下载地址：

https://downloads.mysql.com/archives/community/

#本次使用5.7.32的版本

1）卸载mariadb数据库以防止冲突

yum remove mariadb*

2）上传rpm包至/root，并解压

tar -xvf /root/mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar

3）安装rpm

yum -y install mysql-comm*

4）启动MySQL

systemctl start mysqld.service

systemctl enable mysqld.service

---

MySQL安装-B.手动方式

1）卸载mariadb数据库以防止冲突

yum remove mariadb*

2）创建mysql组和用户名

groupadd -r mysql

useradd -g mysql

3）上传、解压并重命名

tar -zxvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/mysql

4）创建数据库data目录

cd /usr/local/mysql

mkdir data

chown -R mysql.mysql .

chmod 755 data

5)编辑my.cnf配置

vi /etc/my.cnf

datadir=/usr/local/mysql/data

basedir=/usr/local/mysql

#[mysqld_safe]

#log-error=/var/log/mariadb/mariadb.log

#pid-file=/var/run/mariadb/mariadb.pid

6）命令模式启动数据库

bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

注：.P1NHjvBj5/r

7）编辑环境变量

vi /root/.bash_profile

修改处1：PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

8）修改生效

. /root/.bash_profile

9）添加mysql服务

cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld

chmod 700 /etc/rc.d/init.d/mysqld

10）启动MySQL

systemctl start mysqld.service

systemctl enable mysqld.service

---

MySQL配置

1）查看初始密码

cat /var/log/mysqld.log |grep password

2）修改密码

mysql -uroot -p'**********'    #例如mysql -uroot -p‘12345678’ 

mysql> alter user 'root'@'localhost'IDENTIFIED BY '************';   

#例如mysql> alter user 'root'@'localhost'IDENTIFIED BY ‘12345678’;

3）赋予远程访问权限

mysql> grant select on *.* to root@"%" identified by "************";

#例如mysql> grant select on *.* to root@"%" identified by "12345678";

---

MySQL主从部署

master库操作

1）编辑配置文件

vim /etc/my.cnf

添加以下两条内容：

server-id=2

log_bin=mysql-bin

2）重启MySQL

service mysqld restart

3）登录MySQL，查看database

mysql -uroot -p

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| operation_center   |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

初始库为information_schema、performance_schema、mysql、sys

4）查看mysql主服务器bin_log日志信息，用于主从配置

mysql> show master status;

+------------------+-----------+--------------+------------------+-------------------+

| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+-----------+--------------+------------------+-------------------+

| mysql-bin.000020 | 419437813 |              |                  |                   |

+------------------+-----------+--------------+------------------+-------------------+

5）创建远程用户slave并赋予权限

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%'   IDENTIFIED BY '***';

6）备份dump文件，并传给salve库服务器

mysqldump -uroot -proot --all-databases >/tmp/all.sql

---

slave库操作

1）编辑配置文件

vim /etc/my.cof

添加以下两条内容：

server-id=3

log_bin=mysql-bin

2）重启MySQL

service mysqld restart

3）创建备库并导入数据

mysql> create database abc;

mysql> use abc;

mysql> set names utf8;

mysql> source abc.sql

4）重新登陆MySQL

mysql -uroot -p’***********’

5）配置主从关系

mysql> change master to master_host='master IP', master_user='slave', master_password='slave PASSWD',master_log_file='mysql-bin.000020',master_log_pos=419437813;

6）开启主从同步

mysql> start slave;

7）查看slave状态

mysql> show slave status\G

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

IO和SQL线程运行都为YES 表示主从建立完成

关闭主从

close slave;

修改最大连接数为1000

vi /etc/my.cnf

[mysqld]

……

max_connections = 1000

查看最大连接数

sql> SHOW VARIABLES LIKE 'max_connections';