MySQL的基本安装与部署
#1.1准备
获得软件地址：https://downloads.mysql.com/archives/community/
##1.2.MySQL部署安装
1.解压
tar xf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz
2.软连接
shell> ln -s /data/app/mysql-8.0.29-linux-glibc2.12-x86_64 /usr/local/mysql
3. 环境清理
shell> yum remove -y mariadb-libs
shell> yum install -y libaio-devel
4. 创建用户
shell> useradd mysql
5. 创建目录
shell> mkdir -p /data/mysql3306/
6. 授权
shell> mkdir -p /data/mysql3306/{log,etc,tmp,data}
shell> chown -R mysql.mysql /data/mysql3306/
7. 初始化数据
shell> vim /etc/profile
shell> export PATH=/usr/local/mysql/bin:$PATH
shell> source /etc/profile
shell> mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql3306/data
8. 配置文件
vim /data/mysql3306/etc/my.cnf
[mysql]
# CLIENT #
no_auto_rehash
port = 3306
socket = /data/mysql3306/tmp/mysql.sock
default_character_set = utf8mb4
prompt = "\u:\p:\d>"
[mysqld]
user = mysql
port = 3306
default_storage_engine = InnoDB
character_set_server = utf8mb4
init_connect = 'SET NAMES utf8mb4'
transaction_isolation = REPEATABLE-READ
basedir = /usr/local/mysql
datadir = /data/mysql3306/data
socket = /data/mysql3306/tmp/mysql.sock
pid_file = /data/mysql3306/data/mysql.pid
tmpdir = /data/mysql3306/tmp/
slave_load_tmpdir = /data/mysql3306/tmp/
### LOGGING ###
#log_error_verbosity = 2
log_error = /data/mysql3306/log/mysql.err
general_log = 0
general_log_file = /data/mysql3306/log/mysql.log
9. 启动数据库
touch /data/mysql3306/log/mysql.err
chown -R mysql.mysql /data/mysql3306
mysqld_safe --defaults-file=/data/mysql3306/etc/my.cnf --user=mysql &
10.连接数据库
mysql -uroot -p -S /data/mysql3306/tmp/mysql.sock #接口连接
mysql -uroot -p -hlocalhost -P 3306 #TCP链接
11.关闭数据库
mysqladmin shutdown -S /data/mysql3306/tmp/mysql.sock
##1.3.如何关闭query cache
show variables like 'query_cache%';
set global query_cache_size = 0;
##1.4.内存架构
共享内存
show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');
session内存
show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');
设置密码信息
利用命令设置密码
mysqladmin -uroot password '123456'
利用SQL语句设置密码
alter user root@'localhost' identified by '123456';
数据库服务初始化过程
mysqld --initialize-insecure --user=mysql --datadir=/data/3306/data --basedir=/usr/local/mysql
--initialize-insecure 可以实现不安全初始化 数据库没有默认密码设置（免密登录）
--initialize 可以实现安全初始化 数据库会有默认密码设置 后续也需要重新设置
--user=mysql 指定创建数据库默认数据属主和属组 以及指定数据库进程管理用户信息
--datadir 指定数据库存储和加载数据目录
--basedir 指定数据库二进制程序目录
MySQL企业级部署管理
##3.1.基础使用
#第一种启动方式
mysqld_safe --defaults-file=/data/mysql3306/etc/my.cnf --user=mysql &
#第二种启动方式
mysqld --defaults-file=/data/mysql3306/etc/my.cnf --user=mysql &
#两种登入方式
mysql -uroot -p -hlocalhost -P 3306
mysql -uroot -p -S /data/mysql3306/tmp/mysql.sock
#创建新的用户
mysql> create user qianlong@'localhost' identified by '123456';
mysql> grant all on *.* to qianlong@'localhost' with grant option;
mysql -uqianlong -p123456 -S /data/mysql3306/tmp/mysql.sock
#关机
mysqladmin shutdown -S /data/mysql3306/tmp/mysql.sock
mysql -uqianlong -p123456 -S /data/mysql3306/tmp/mysql.sock -e "shutdown"
kill -9 mysqlpid
##3.2.systemctl管理mysql
#1.建立systemctl管理文件
cat > /usr/lib/systemd/system/mysql3306.service <<EOF
[Unit]
Description=MySQL Server
After=network.target
[Install]
WantedBy=multi-user.target
[Service]
Type=forking
TimeoutSec=0
PermissionsStartOnly=true
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql3306/etc/my.cnf --daemonize
LimitNOFILE = 65535
Restart=on-failure
RestartSec=10
RestartPreventExitStatus=1
PrivateTmp=false
EOF
#2.添加可执行权限
chmod 755 /usr/lib/systemd/system/mysql3306.service
#3.重载 systemctl units
systemctl daemon-reload
#4.设置为开机自启动
systemctl enable mysql3306.service
#5.启动与关闭Mysql实例
systemctl start mysql3306.service
企业版mysql打包流程
#1.安装fpm
##安装ruby语言
yum -y install ruby-devel gcc make rpm-build rubygems
curl -sSL https://github.com/rvm/rvm/tarball/stable -o rvm-stable.tar.gz
tar -xzvf rvm-stable.tar.gz
cd rvm-rvm-cc69ed9
./install --auto-dotfiles
source /usr/local/rvm/scripts/rvm
rvm install 2.6.3
rvm use 2.6.3 --default
##安装fpm软件
gem sources --add http://mirrors.aliyun.com/rubygems/
gem sources --remove https://rubygems.org/
gem sources -l
gem install fpm -v 1.4.0
fpm --help
#2.打包
cd /data/app
tar xf mysql-8.0.24-linux-glibc2.12-x86_64.tar.xz
cp mysql-8.0.24-linux-glibc2.12-x86_64 ./mysql8024 -rp
fpm -s dir -t rpm -n mysql8024 --epoch 1 -v 1.0.0 -C /data/app/mysql8024 --verbose --prefix /data/app/mysql8024
#3.上传到公司的YUM源
ls mysql8024-1.0.0-1.x86_64.rpm
##上传到yum仓库后 直接安装即可
yum install mysql8024
#4.打包空实例包
##mysql内部操作
reset master;
shutdown;关机
##清理不需要的文件
cd /data/mysql3306/data
rm -fr binlog*
rm -rf auto.cnf
rm -f undo_00*
rm -f ib_logfile*
##清理日志
cd /data/mysql3306/log
echo ""> mysql.err
echo ""> mysql-slow.log
#打包
chown mysql.mysql /data/mysql3306 -R
cd /data/mysql3306/
tar -czvf MySQL3306.tar.gz etc log tmp data
如何创建用户信息
create user 用户名@‘白名单’; -- 此用户登录是免密登录
create user 用户名@‘白名单’ identified by '密码'; -- 完整创建用户信息
创建本地登录用户：xiaoA 123456
create user xiaoA@'localhost' identified by '123456';
创建远程登录用户: xiaoB 123456 (10.0.0.52)
create user xiaoB@'10.0.0.52' identified by '123456';
create user xiaoB@'test' identified by '123456';
create user xiaoB@'10.0.0.%' identified by '123456';
本地用户登录：
mysql -u用户名称 -p密码信息
远程用户登录：
mysql -u用户名称 -p密码信息 -h数据库服务端地址 -P数据库服务端口
如何查看用户信息
mysql> select user,host,authentication_string from mysql.user; -- 查看所有用户信息 类似cat /etc/passwd
mysql> select user(); -- 查看当前登录用户信息
如何删除用户信息
drop user 用户名@‘白名单’;
drop user test@'10.0.0.52';
或者
delete from mysql.user where user='test' and host='20.0.0.%'; -- 此命令慎用
#操作前检查用户是否有连接,分析连接池
SELECT SUBSTRING_INDEX(host, ':', 1) AS ip, COUNT(*) AS connections
FROM information_schema.processlist
GROUP BY ip
ORDER BY connections DESC;
每条记录一小点~~
1.01^2==1.0201
浙公网安备 33010602011771号