mysql二进制安装
安装服务器
服务器Centos7.9
mysql版本号mysql5.7.29
2、安装前检查项
2.1、关闭防火墙和selinux
修改/etc/selinux/config文件中的SELINUX=disabled
# getenforce
Disabled
# setenforce 0
setenforce: SELinux is disabled
然后重启服务器生效
关闭防火墙
systemctl stop firewalld.service
查看防火墙状态
systemctl status firewalld.service
2.2、swap分区的设置
编辑/etc/sysctl.conf，设置vm.swappiness=10
说明：
swappiness的值越大，表示越积极使用swap分区，越小表示越积极使用物理内存。默认值swappiness=60。
比如：
设置10，表示物理内存剩余10%，就开始使用swap。
设置80，表示物理内存剩余80%，就开始使用swap。
2.3、操作系统的限制
编辑/etc/security/limits.conf，添加
root soft nproc 65536
root soft nproc 65536
root soft nofile 65536
root hard nofile 65536
mysql soft nproc 65536
mysql soft nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
2.4. 关闭numa
简单来讲关闭numa功能，可以更好地分配内存，不需要采用swap的方式来获取内存。原因是使用swap可能会导致数据库性能急剧下降。关闭方式也分在BIOS、操作系统中关闭，或者是在数据库启动过程中关闭。
2.5、centos7会自带一个mariadb,是mysql的一个分支，需要清理掉
查看是否已安装
# rpm -qa |grep -i mariadb
mariadb-libs-5.5.68-1.el7.x86_64
卸载命令
未卸载mariadb安装时会出现下面的问题：
安装第一个rpm -ivh mysql-community-common-5.7.10-1.el7.x86_64.rpm 与mariadb的文件冲突
# rpm -e --nodeps mariadb-libs
2.6、检查 MySQL 指令
# rpm -qa|grep -i mysql
如果有，就先全部卸载，命令如下：
# yum -y remove mysql-libs.x86_64
2.7、查找mysql目录是否存在，存在就删除
# find / -name mysql
删除
# rm -rf /usr/local/mysql
# rm -rf /data/mysql
2.8、检查mysql用户是否存在，如存在，就删掉
# id mysql 检查mysql用户是否存在
# userdel mysql 删除mysql用户
# groupdel mysql 删除mysql用户组
3、安装mysql，解压缩二进制安装文件
3.1解压MySQL包到 /usr/local目录
# cd /soft 进到mysql安装文件目录
# tar zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ 解压缩安装文件
# cd /usr/local
# mv /usr/local/mysql-5.7.29-linux-glibc2.12-x86_64 /usr/local/mysql
创建mysql用户以及用户组
查看是否已经创建了：
# cat /etc/passwd | grep mysql
# cat /etc/group | grep mysql
没有创建则新建mysql用户以及用户组：
# groupadd mysql
# useradd -r -g mysql mysql //使用-r参数表示mysql用户是一个系统用户，不能登录
# id mysql
uid=996(mysql) gid=1000(mysql) groups=1000(mysql)
5、创建相应的目录
# mkdir -p /data/mysql/data --创建mysql数据存放目录
# mkdir -p /data/mysql/logs --创建mysql日志存放目录
# mkdir -p /data/mysql/binlog
# mkdir -p /data/mysql/ibdata
6、文件夹赋权限
（注意：data文件夹一定要是mysql权限，否则后期启动mysql生成mysql.pid或mysql.sock文件会有问题）
# chown -R mysql:mysql /usr/local/mysql
# chown -R mysql:mysql /data/mysql/data/
# chown -R mysql:mysql /data/mysql/logs/
# chown -R mysql:mysql /data/mysql/binlog/
# chown -R mysql:mysql /data/mysql/ibdata/
# chown -R mysql:mysql /data/
7、mysql加入环境变量
设置环境变量:
# vi /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
# source /etc/profile
编辑好的my.cnf上传到/etc/目录下
注意，供应链正式服务器下面3个标黄色参数是屏蔽的。
#validate_password=OFF 屏蔽掉，初始化后mysql库才能起来
vi /etc/my.cnf
[client]
socket = /data/mysql/data/mysql.sock
port = 3306
[mysqld]
explicit_defaults_for_timestamp = ON
port = 3306
datadir = /data/mysql/data
socket = /data/mysql/data/mysql.sock
pid-file = /data/mysql/logs/mysql.PID
user = mysql
symbolic-links=0
max_allowed_packet = 128M
character-set-server = utf8mb4
skip_external_locking = OFF
skip_name_resolve = ON
default_time_zone = "+8:00"
log_timestamps = SYSTEM
log_error_verbosity = 3
log_error =/data/mysql/logs/mysqld.log
slow_query_log = ON
long_query_time = 1
slow_query_log_file = /data/mysql/logs/mysql-slow.log
lower_case_table_names = 1
default-storage-engine = INNODB
innodb_data_home_dir = /data/mysql/data
innodb_buffer_pool_size = 1G
#innodb_thread_concurrency = 16
innodb_log_files_in_group = 3
innodb_log_file_size = 128M
innodb_file_per_table = ON
#sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_thread_concurrency = 16
#innodb_max_dirty_pages_pct = 90
innodb_flush_neighbors = 0
innodb_io_capacity = 1000
innodb_io_capacity_max = 2000
back_log = 500
max_connections = 2000
#max_user_connections= 1990
table_open_cache = 5000
group_concat_max_len = 102400
server_id = 174
binlog_format = row
binlog_cache_size = 16M
log_bin_trust_function_creators = 1
log_bin_index = /data/mysql/logs/mysql-bin.index
log_bin = /data/mysql/logs/mysql-bin
relay_log_index = /data/mysql/logs/mysql-relay.index
relay_log = /data/mysql/logs/mysql-relay
gtid_mode = ON
enforce_gtid_consistency = ON
log-slave-updates
expire_logs_days = 7
#skip_slave_start
key_buffer_size = 32M
myisam_sort_buffer_size = 128M
myisam_repair_threads = 4
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
tmp_table_size = 128M
max_heap_table_size = 128M
#plugin-load-add=validate_password.so
#validate_password=OFF
[mysqldump]
quick
max_allowed_packet = 128M
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 8M
write_buffer = 8M
初始化
如果搭建的是从库，这里不要初始化，也不要启库，先把主库的数据文件通过xtrabackup拷贝到从库
这里报错了，原因是初始化时没有先把/etc/my.cnf拷贝进去，要先拷贝my.cnf文件，再初始化，否则会报错。
mysql> flush privileges;
ERROR 1146 (42S02): Table 'mysql.servers' doesn't exist
mysql初始化
# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/data
10、加入MySQL为系统服务
拷贝文件并重命名为mysqld
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# chmod 755 /etc/init.d/mysqld
# chkconfig mysqld on
# chkconfig --list|grep mysqld
Note: This output shows SysV services only and does not include native
systemd services. SysV configuration data might be overridden by native
systemd configuration.
If you want to list systemd services use 'systemctl list-unit-files'.
To see services enabled on particular target use
'systemctl list-dependencies [target]'.
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@19Ctest-csposserver:/]#
11、启动mysql
# service mysqld start
查看启动后mysql状态
[root@19Ctest-csposserver:/]# service mysqld status
SUCCESS! MySQL running (23049)
查询mysql初始密码
# more /data/mysql/logs/mysqld.log | grep 'password';
2023-12-29T14:23:32.311750+08:00 1 [Note] A temporary password is generated for root@localhost: /:azEg*w3q/b
13、登录mysql，重设密码，创建新用户
以初始密码登录mysql
# mysql -uroot -p'/:azEg*w3q/b'
先重设root用户密码
mysql> alter user 'root'@'localhost' identified by 'mysql';
mysql> flush privileges;
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
让所有ip都能进入访问
grant all privileges on *.* to 'root'@'%' identified by 'mysql'; // 让所有IP都能进入访问
可以看到新建了’root’@’%’用户
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
删除root 的localhost用户
mysql> delete from mysql.user where user='root' and host='localhost';
mysql> flush privileges;
mysql> select user,host from mysql.user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
