99-install_binary

• MySQL--mysqld、mysql_safe、mysql.server、mysqladmin几种启动和停止服务使用说明
• 【MySQL】关于MySQL启动后mysqld_safe和mysqld进程

1. download

• MySQL Product Archives
• Compressed TAR Archive版本即可

2. install

1. tar.gz

cd ~/al/soft/mysql

# 添加mysql用户
useradd mysql
# useradd: user 'mysql' already exists

# 解压
tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
# 软链接
ln -s ~/al/soft/mysql/mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql

2. profile

# 环境变量
vi /etc/profile
# 引用《环境变量》
source /etc/profile
echo $PATH

# mysql版本
mysql -V

export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin

3. cnf

# 数据目录及授权
mkdir -p ~/al/soft/mysql/3306/data
chown mysql.mysql ~/al/soft/mysql/3306

vim /etc/my.cnf

• 《最小配置》

[mysqld]
user=root
basedir=/usr/local/mysql
datadir=/root/al/soft/mysql/3306/data
socket=/root/al/soft/mysql/3306/mysql.sock
server_id=51

[mysql]
socket=/root/al/soft/mysql/3306/mysql.sock

3. initialize

# 1. 建3306默认库
mysqld --initialize
mysqld --initialize-insecure # 创建未加密的超级用户账号，需要及时设置密码


# 2. 建立3307库
./mysqld --initialize-insecure --user=root --datadir=/root/al/soft/mysql/3307/data --basedir=/root/al/soft/mysql/mysql-5.7.37-linux-glibc2.12-x86_64

 [Warning] [MY-010453] [Server] root@localhost is created with an empty password! Please consider switching off the --initialize-insecure option.

# 3. 两种方法的区别：
--initialize: 初始化时，会自动创建超级管理员（root@'localhost'）,生成随机密码，12位，4种密码复杂度。这个密码，需要在第一次登陆时修改掉才可以正常管理数据

[root@localhost data]# mysqld --initialize-insecure
2022-06-20T03:28:50.032623Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-20T03:28:53.123888Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-20T03:28:54.137313Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-20T03:28:54.753227Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 1cc40b2c-f049-11ec-b33b-525400a81562.
2022-06-20T03:28:54.773455Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-20T03:28:56.228208Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-06-20T03:28:56.228238Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-06-20T03:28:56.229865Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-20T03:28:56.558047Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

1. error

# 如果遇到以下报错：
[ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
rm -rf ~/al/soft/mysql/3306/data

[ERROR] libaio ......

# 安装 libaio-devel
yum install -y libaio-devel

4. start_stop

# 1. mysql.server
cd /usr/local/mysql/support-files/
./mysql.server start

# 2. SYS-V启动方式
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
service mysqld restart | stop | start | status

# 3. 推荐 mysqld_safe
cd /usr/local/mysql/bin

mysqld_safe --defaults-file=/root/al/soft/mysql/3307/3307.cnf --user=root &
mysqld_safe --defaults-file=/root/al/soft/mysql/3306/3306.cnf --user=root &

mysqladmin -uroot -p -S /root/al/soft/mysql/3307/mysql.sock shutdown
mysqladmin -uroot -p -S /root/al/soft/mysql/3306/mysql.sock shutdown

5. login

# 安装后登陆
mysql

# 首次登录修改密码
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

set password='root';

# 查看edition
select version();

3. remote_login

• Linux中mysql实现远程访问

# 本地登录
mysql -u root -p

# 远程登录
mysql -uroot -proot -h192.168.oo.xx -P3307

# sql文件导入
source [path]

# 建库
drop database if exists `ooxx`;
create database `ooxx` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

use mysql;
select 'Host','User' from user;
UPDATE user SET Host= '%' WHERE User= 'root' LIMIT 1;
flush privileges;

4. uninstall

• linux怎么卸载mysql？

# RPM是Red-Hat Package Manager（红帽软件包管理器）缩写
# 1. 首先查看mysql的安装情况
rpm -qa | grep -i mysql

# MySQL-client-5.5.25a-1.rhel5
# MySQL-server-5.5.25a-1.rhel5

# 2. 停止mysql服务，并删除包
rpm -ev MySQL-client-5.5.25a-1.rhel5
rpm -ev MySQL-server-5.5.25a-1.rhel5

# 3.1. 如果提示依赖包错误，则使用以下命令尝试
rpm -ev --nodeps MySQL-client-5.5.25a-1.rhel5
# 3.2. 如果提示错误：error: %preun(xxxxxx) scriptlet failed, exit status 1
rpm -e --noscripts MySQL-client-5.5.25a-1.rhel5

# 4. 查找老版本mysql的目录、并且删除老版本mysql的文件和库
find / -name mysql
# 查找结果如下：
/run/lock/subsys/mysql
/etc/rc.d/init.d/mysql
/etc/selinux/targeted/active/modules/100/mysql
/usr/lib64/mysql
/usr/share/mysql
/usr/local/mysql
/usr/local/mysql/mysql
/usr/local/mysql/mysql/bin/mysql
/usr/local/mysql/mysql/include/mysql
/usr/local/mysql/mysql/data/mysql
/usr/local/mysql/mysql/d3007/mysql

# 删除对应的mysql目录
rm -rf /run/lock/subsys/mysql
rm -rf /etc/rc.d/init.d/mysql
rm -rf /etc/selinux/targeted/active/modules/100/mysql
rm -rf /usr/lib64/mysql
rm -rf /usr/share/mysql
rm -rf /usr/local/mysql
rm -rf /usr/local/mysql/mysql
rm -rf /usr/local/mysql/mysql/bin/mysql
rm -rf /usr/local/mysql/mysql/include/mysql
rm -rf /usr/local/mysql/mysql/data/mysql
rm -rf /usr/local/mysql/mysql/d3007/mysql

# 5. 注意：卸载后/etc/my.cnf不会删除，需要进行手工删除
rm -rf /etc/my.cnf

5. my-default.cnf

• Server Configuration Defaults

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.

[mysqld]

# Remove leading ## and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

# Remove leading ## to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the ## and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....

# Remove leading ## to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

6. pwd_modify

use mysql;

# 5.7及以后
update user set authentication_string = password('root') where user = 'root';

flush privileges;

98. 双版本运行

1. 因为环境变量的原因，命令前一定要加./，来指定路径
2. 配置文件一定指定port
3. 第一次登录，指定ip、port。据说localhost，sock位置不对

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

[client]
socket=/Users/listao/soft/mysql/mysql8/3306/mysql.sock

[mysqld]
user=root
port=3307
basedir=/Users/listao/soft/mysql/mysql-8.0.31-macos12-x86_64
datadir=/Users/listao/soft/mysql/mysql8/3306/data
socket=/Users/listao/soft/mysql/mysql8/3306/mysql.sock
server_id=81

[mysql]
socket=/Users/listao/soft/mysql/mysql8/3306/mysql.sock

# 查看mysql进程
ps -ef | grep mysql


# 1. 初始化mysql
./mysqld --initialize-insecure --user=root --datadir=/Users/listao/soft/mysql/mysql8/3307/data --basedir=/Users/listao/soft/mysql/mysql-8.0.31-macos12-x86_64

# 2. 指定配置文件启动DB
./mysqld_safe --defaults-file=/Users/listao/soft/mysql/mysql8/3307/3307.cnf --user=root &

# 3. 第一次登录
./mysql -uroot -h127.0.0.1 -P3307

# 4. 修改密码
set password='root';

# 5. 停DB
./mysqladmin -uroot -p -S /Users/listao/soft/mysql/mysql8/3307/mysql.sock shutdown

99. bug

1. /tmp/ib0n3frL

/usr/local/mysql/bin/mysqld: Can't create/write to file '/tmp/ib0n3frL' (Errcode: 13 - Permission denied)

chmod 777 /tmp

2. edition

[ERROR] [FATAL] InnoDB: Table flags are 0 in the data dictionary but the flags in file ./ibdata

版本问题，data目录被8.0占用了，重新mysqld --initialize-insecure一下。启动不同edition一定要，选择不同cnf，cnf里的data不能一样