Mysql 集群技术

一、Mysql 在服务器中的部署方法

在企业中90%的服务器操作系统均为Linux
在企业中对于Mysql的安装通常用源码编译的方式来进行
官网：http://www.mysql.com

1.1 在Linux下部署mysql

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #指定安装路径
-DMYSQL_DATADIR=/data/mysql \ #指定数据目录
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \ #指定套接字文件
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #指定启用INNODB存储引擎，默认用myisam
-DWITH_EXTRA_CHARSETS=all \ #扩展字符集
-DDEFAULT_CHARSET=utf8mb4 \ #指定默认字符集
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \ #指定默认校验字符集
-DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/ #指定c++库依赖make -j2 #-j2 表示有几个核心就跑几个进程

1.1.1 安装依赖性

#下载源码安装需要用到的软件[root@mysql-node1 ~]# yum  install cmake gcc-c++ openssl-devel -y
[root@mysql-node1 ~]# yum install ncurses-devel.x86_64   rpcgen.x86_64  -y#导入libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm
[root@mysql-node1 ~]# yum install libtirpc-devel-0.2.4-0.16.el7.x86_64.rpm -y

1.1.2 下载并解压源码包

#导入mysql-boost-5.7.44.tar.gz
[root@mysql-node1 ~]# tar zxf mysql-boost-5.7.44.tar.gz

1.1.3 源码编译安装mysql

[root@mysql-node1 ~]# cd mysql-5.7.44/[root@mysql-node1 mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/ [root@mysql-node1 mysql-5.7.44]# make -j2 [root@mysql-node10 mysql-5.7.44# make install

注意！！！（当cmake出错后如果想重新检测，删除 mysql-5.7.44 中 CMakeCache.txt即可）

1.2 部署mysql

#生成启动脚本
[root@node10 ~]# dnf install initscripts-10.11.6-1.el9.x86_64 -y
[root@node10 ~]# cd /usr/local/mysql/support-files/
[root@node10 support-files]# cp mysql.server /etc/init.d/mysqld
#修改环境变量
[root@node10 ~]# vim ~/.bash_profile
export PATH=$PATH:/usr/local/mysql/bin
[root@node10 ~]# source ~/.bash_profile
#生成配置文件
[root@node10 support-files]# yum install mariadb-connector-c-config-3.2.6-
1.el9_0.noarch -y
[root@node10 my.cnf.d]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql #指定数据目录
socket=/data/mysql/mysql.sock #指定套接字
symbolic-links=0 #数据只能存放到数据目录中，禁止链接到数据目录
#数据库初始化建立mysql基本数据
[root@node10 ~]# mysqld --initialize --user=mysql
[root@node10 ~]# /etc/init.d/mysqld start
[root@node10 ~]# chkconfig mysqld on

1.2.1 生成启动脚本

[root@mysql-node1 ~]# cd /usr/local/mysql/
[root@mysql-node1 mysql]# ls
bin  docs  include  lib  LICENSE  man  mysql-test  README  README-test  share  support-files
[root@mysql-node1 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql-node1 mysql]# mkdir /data/mysql -p
[root@mysql-node1 mysql]# chown mysql.mysql -R /data/mysql
[root@mysql-node1 mysql]# pwd
/usr/local/mysql
[root@mysql-node1 mysql]# ls
bin  docs  include  lib  LICENSE  man  mysql-test  README  README-test  share  support-files
[root@mysql-node1 mysql]# cd /usr/local/mysql/support-files/
[root@mysql-node1 support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@mysql-node1 support-files]# cp mysql.server /etc/init.d/mysqld

1.2.2 修改环境变量

[root@mysql-node1 support-files]# vim ~/.bash_profile 
[root@mysql-node1 support-files]# cat ~/.bash_profile 
# .bash_profile# Get the aliases and functions
if [ -f ~/.bashrc ]; then. ~/.bashrc
fi# User specific environment and startup programsPATH=$PATH:$HOME/bin:/usr/local/mysql/binexport PATH
[root@mysql-node1 support-files]# source ~/.bash_profile

1.2.3 生成配置文件

[root@mysql-node1 support-files]# vim /etc/my.cnf
[root@mysql-node1 support-files]# cat /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0

1.2.4 数据库初始化建立mysql基本数据

[root@mysql-node1 ~]# mysqld --user mysql --initialize
[root@mysql-node1 ~]# /etc/init.d/mysqld start

1.2.5 查询所有服务状态以及设置MySQL服务启动

[root@mysql-node1 ~]# yum install chkconfig -y
[root@mysql-node1 ~]# chkconfig --list[root@mysql-node1 ~]# chkconfig  mysqld on
[root@mysql-node1 ~]# chkconfig --list

1.2.6 数据库安全初始化

[root@mysql-node1 ~]# mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root: #输入当前密码
The existing password for the user account root has expired. Please set a new password.
New password: #输入新密码
Re-enter new password: #重复密码
VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: no #是否启用密码插件
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no
#是否要重置密码
... skipping.
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? (Press y|Y for Yes, any other key for No) : y
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? (Press y|Y for Yes, any other key for No) : y
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? (Press y|Y for Yes, any other key for No): y
- 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? (Press y|Y for Yes, any other key for No) : y
Success.

1.2.7 测试

[root@mysql-node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.44 Source distributionCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

二、mysql的主从复制

[root@mysql-node1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
symbolic-links=0
log-bin=mysql-bin
server-id=1
[root@mysql-node1 ~]# /etc/init.d/mysqld restart