1-Mysql 安装前 OS（Linux）优化
1.1 配置主机名
echo '10.0.0.100 itpuxmysqldb' >> /etc/hosts
1.2 设置英文语言环境
echo "export LANG=en_US.UTF-8" >> /etc/profile
source /etc/profile
1.3 设置资源参数
# 配置 /etc/security/limits.conf 文件内容（针对Linux7及以下）
cat >> /etc/security/limits.conf << EOF
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535
* soft stack 65535
* hard stack 65535
EOF
echo '* - nproc 65535' > /etc/security/limits.d/90-nproc.conf
# 配置 /etc/security/limits.conf 文件内容（针对Linux8及以上）
cat >> /etc/security/limits.conf << EOF
* soft nofile 1024000
* hard nofile 1024000
* soft nproc 1024000
* hard nproc 1024000
* soft core unlimited
* hard core unlimited
* soft memlock unlimited
* hard memlock unlimited
EOF
echo '* - nproc 16384' > /etc/security/limits.d/90-nproc.conf
1.4 关闭 SELinux 与 firewalld
# 关闭SELinux
sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config
# 关闭防火墙（firewalld 或 iptables:针对Linux 7一下，不包括 Linux7）
systemctl stop firewalld.service
systemctl disable firewalld.service
service iptables stop
chkconfig --level 2345 iptables off
1.5 控制资源分配限制
echo "session required pam_limits.so" >> /etc/pam.d/login
1.6 NUMA功能关闭
# Linux 6及以下如下操作：编辑 /etc/grub.conf 在 quit 所在行中末尾中追加 numa = off
# Linux 7及以上可以如下操作
line_num=`cat -n /etc/default/grub | grep 'GRUB_CMDLINE_LINUX' |awk '{print $1}'|head -n 1`
sed -i --follow-symlinks 's/numa=off//g' /etc/default/grub
sed -i --follow-symlinks 's/transparent_hugepage=never//g' /etc/default/grub
sed -i --follow-symlinks ""${line_num}" s/\"$/ numa=off\"/g" /etc/default/grub
sed -i --follow-symlinks ""${line_num}" s/\"$/ transparent_hugepage=never\"/g" /etc/default/grub
grub2-mkconfig -o /boot/grub2/grub.cfg
1.7 IO调度算法优化
# 在 /etc/rc.local 文件中追加如下内容：
# 1-如果有多块磁盘按照如下配置进行
# 2-scheduler[IO调度算法]、read_ahead_kb[预读]、nr_requests[队列]
echo 'deadline' > /sys/block/sdb/queue/scheduler
echo '16' > /sys/block/sdb/queue/read_ahead_kb
echo '512' > /sys/block/sdb/queue/nr_requests
1.8 虚拟内存与保留内存配置
# Linux7*系版本及以下配置如下：
# 在 /etc/sysctl.conf 文件中追加如下内容
# swappiness 表示当内存仅剩余10%启用swap交换空间
# min_fre_kbytes 表示最少内存可剩余50M
vm.swappiness=10
vm.min_free_kbytes=51200
# Linux8*系及以上更高版本配置如下：
# file
fs.aio-max-nr = 2097152
fs.file-max = 76724600
fs.nr_open= 20480000
# vm
vm.swappiness=1
vm.min_free_kbytes = 204800
vm.overcommit_memory = 0
vm.overcommit_ratio = 90
vm.dirty_background_bytes = 409600000
vm.dirty_expire_centisecs = 3000
vm.dirty_ratio = 95
vm.dirty_writeback_centisecs = 100
vm.mmap_min_addr = 65536
vm.zone_reclaim_mode = 0
vm.nr_hugepages = 750
#kernel
kernel.shmmax = 3221225472
kernel.shmall = 786432
kernel.shmmni = 4096
kernel.sem = 4096 2048000 200 32768
kernel.numa_balancing = 0
# tcp-part1-load-balancer
net.ipv4.ip_forward = 1
net.ipv4.ip_nonlocal_bind = 1
net.netfilter.nf_conntrack_max = 1048576
net.ipv4.ip_local_port_range = 40000 65535
net.ipv4.tcp_max_tw_buckets = 262144
net.core.somaxconn = 16384
net.ipv4.tcp_max_syn_backlog = 8192
net.core.netdev_max_backlog = 10000
#tcp-part2-buffer
net.ipv4.tcp_rmem = 8192 65536 16777216
net.ipv4.tcp_wmem = 8192 65536 16777216
net.ipv4.tcp_mem = 8388608 12582912 16777216
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 4194304
# tcp-part3-keepalive
net.ipv4.tcp_keepalive_intvl = 20
net.ipv4.tcp_keepalive_probes = 3
net.ipv4.tcp_keepalive_time = 60
# tcp-part4-port resure
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 5
net.ipv4.tcp_timestamps = 1
# tcp-part4-anti-flood
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_synack_retries = 2
net.ipv4.tcp_syn_retries = 1
1.9 本地 yum 安装源配置
# 本地镜像仓库安装源配置
## 挂载安装镜像光盘
mount /dev/cdrom /mnt
## 添加开机自动挂载文件/etc/fstab
echo "/dev/cdrom /mnt iso9660 defaults 0 0" >> /etc/fstab
## 创建安装源配置文件local.repo
vim /etc/yum.repos.d/local.repo
### 内容如下
[local]
name = Local YUM Repo
baseurl = file:///mnt
gpgcheck = 0
### 配置测试命令
### 清楚本地缓存
yum clean all
### 建立安装缓存
yum makecache
2-Mysql 安装前配置操作
2.1 存储规划配置
# 使用LVM方式进行磁盘分区管理
## 创建物理卷
pvcreate /dev/sdb
## 创建卷组
vgcreate dbvg /dev/sdb
## 创建逻辑卷
lvcreate -n mysqllv -L 50G dbvg
lvcreate -n datalv -L 50G dbvg
lvcreate -n loglv -L 50G dbvg
lvcreate -n baklv -L 100G dbvg
## 逻辑卷分区格式化操作
mkfs.xfs /dev/dbvg/mysqllv
mkfs.xfs /dev/dbvg/datalv
mkfs.xfs /dev/dbvg/loglv
mkfs.xfs /dev/dbvg/baklv
## 创建分区挂载点
mkdir -p /mysql/{app,data,log,backup}
## 将分区与挂载点写入开机自启配置文件
echo "/dev/dbvg/mysqllv /mysql/app xfs defaults,noatime,nodiratime 0 0" >> /etc/fstab
echo "/dev/dbvg/datalv /mysql/data xfs defaults,noatime,nodiratime 0 0" >> /etc/fstab
echo "/dev/dbvg/loglv /mysql/log xfs defaults,noatime,nodiratime 0 0" >> /etc/fstab
echo "/dev/dbvg/baklv /mysql/backup xfs defaults,noatime,nodiratime 0 0" >> /etc/fstab
systemctl daemon-reload
mount -a
2.2 mysql 所需依赖包安装
# 安装相关依赖软件包
## Linux6*
yum install gcc bison ncurses ncurses-devel zlib libxml2 openssl openssl-devel libstdc++-devel gcc-c++ libaio libaio-devel numactl lvm2 net-tools wget vim lrzsz -y
## Linux7*
yum install gcc bison ncurses ncurses-devel zlib libxml2 openssl openssl-devel libstdc++-devel gcc-c++ libaio libaio-devel ncurses-compat-libs libtirpc-devel glibc-devel cmake lvm2 net-tools wget vim lrzsz -y
## Linux8*+
dnf -y install ncurses ncurses-devel gcc gcc-c++ openssl-devel libtirpc rpcgen lvm2 net-tools wget vim lrzsz
dnf -y install gcc-toolset-12-gcc gcc-toolset-12-gcc-c++ gcc-toolset-12-binutils gcc-toolset-12-annobin-annocheck gcc-toolset-12-annobin-plugin-gcc
3-Mysql 软件安装
3.1 mysql 软件下载
# 官方下载地址：https://dev.mysql.com/downloads/mysql/
3.2 mysql 二进制版本安装
# 解压安装包
tar -zxvf mysql-[version]-linux-glibc2.12-x86_64.tar.gz -C /mysql/app/
ln -s /mysql/app/mysql-[version] /mysql/app/mysql
3.3 mysql 源码包版本安装
# 源码版本
tar -zxvf mysql-boost-[version].tar.gz -C /opt/
# 编译前检查
cd mysql-[version]
cmake . -DCMAKE_INSTALL_PREFIX=/mysql/app/mysql \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/mysql/data/3306/data \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DSYSCONFDIR=/mysql/data/3306 \
-DMYSQL_UNIX_ADDR=/mysql/data/3306/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
# 建议5.7
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_unicode_ci \
# 建议8.*
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_unicode_ci \
-DWITH_EXTRA_CHARSETS=all \
-DDOWNLOAD_BOOST=1 \
-DWITH_DEBUG=1 \
-DWITH_BOOST=/opt/mysql-[version]/boost/boost_1_59_0
# 源码安装
make -j 2 && make install
# mysql 环境变量配置
# 配置环境变量
echo 'export MYSQL_HOME=/mysql/app/mysql' >> /etc/profile
echo 'export PATH=$MYSQL_HOME/bin:$PATH' >> /etc/profile
source /etc/profile
4-Mysql 初始化操作前配置
4.1 配置 my.cnf 文件,针对 5.7.* 版本的 mysql 软件
# 在规范目录位置进行编写 my.cnf 配置文件
vim /mysql/data/3306/my.cnf
# 内容如下
[client]
port=3306
socket=/mysql/data/3306/mysql.sock
[mysql]
no-beep
prompt="\u@fgedu \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
default-character-set=utf8
[mysqld]
########basic settings########
server-id=3306
port=3306
user = mysql
bind_address= [IP]
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket=/mysql/data/3306/mysql.sock
pid-file=/mysql/data/3306/mysql.pid
character-set-server=utf8
skip-character-set-client-handshake=1
autocommit = 0
#skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
query_cache_type = 1
query_cache_size=1M
table_open_cache=2000
thread_cache_size=768
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
read_buffer_size=8M
read_rnd_buffer_size=4M
back_log=1024
#flush_time=0
open_files_limit=65536
table_definition_cache=1400
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/itpuxdb-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/itpuxdb-query.err
long_query_time=10
log-error=/mysql/log/3306/itpuxdb-error.err
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
#log_bin = "/log/bin_log/binlog"
########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors
########innodb settings########
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
#innodb_flush_method = O_DIRECT
innodb_log_file_size = 200M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_undo_logs = 128
innodb_undo_tablespaces = 3
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
innodb_flush_neighbors = 1
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
#transaction_write_set_extraction=MURMUR32
show_compatibility_56=on
4.2 配置 my.cnf 文件,针对 8.* 版本的 mysql 软件
# 在规范目录位置进行编写 my.cnf 配置文件
[client]
port=3306
socket = /mysql/data/3306/mysql.sock
default-character-set=utf8mb4
[mysql]
no-beep
prompt="\u@fgedu \R:\m:\s [\d]> "
#no-auto-rehash
auto-rehash
skip-binary-as-hex
default-character-set=utf8mb4
[mysqld]
########basic settings########
server-id=3306
port=3306
user = mysql
bind_address= [IP]
basedir=/mysql/app/mysql
datadir=/mysql/data/3306/data
socket = /mysql/data/3306/mysql.sock
pid-file = /mysql/data/3306/mysql.pid
character-set-server=utf8mb4
autocommit = 1
lower-case-table-names=1
#skip_name_resolve = 1
max_connections = 1000
max_connect_errors = 5000
default-storage-engine=INNODB
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
sort_buffer_size = 32M
join_buffer_size = 128M
tmp_table_size = 72M
max_allowed_packet = 16M
#sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
interactive_timeout = 600
wait_timeout = 600
read_buffer_size = 16M
read_rnd_buffer_size = 32M
large-pages=ON
back_log=1024
table_open_cache=2000
thread_cache_size=768
open_files_limit=65536
table_definition_cache=1400
#lock_wait_timeout = 3600
#query_cache_type = 1
#query_cache_size=1M
#flush_time=0
#binlog_row_event_max_size=8K
#sync_master_info=10000
#sync_relay_log=10000
#sync_relay_log_info=10000
##myisam settings
myisam_max_sort_file_size=10G
myisam_sort_buffer_size=135M
key_buffer_size=32M
default_time_zone = "+8:00"
admin_address = '127.0.0.1'
admin_port = 33066
########log settings########
log-output=FILE
general_log = 0
general_log_file=/mysql/log/3306/fgedu-general.err
slow_query_log = ON
slow_query_log_file=/mysql/log/3306/fgedu-query.err
long_query_time=10
log-error=/mysql/log/3306/fgedu-error.err
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_replica_statements = 1
log_throttle_queries_not_using_indexes = 10
binlog_expire_logs_seconds=604800
min_examined_row_limit = 100
log_bin=/mysql/log/3306/binlog/fgedudb-binlog
binlog_rows_query_log_events=on
binlog_gtid_simple_recovery=1
log_timestamps=system
#deprecated:log_slow_slave_statements = 1
#deprecated:expire_logs_days = 90
#deprecated:binlog_format='ROW'
########replication settings########
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
#log_bin = bin.log
#sync_binlog = 1
#gtid_mode = on
#enforce_gtid_consistency = 1
#log_slave_updates
#binlog_format = row
#relay_log = relay.log
#relay_log_recovery = 1
#binlog_gtid_simple_recovery = 1
#slave_skip_errors = ddl_exist_errors
#slave_parallel_type = LOGICAL_CLOCK
#slave_parallel_workers = 32
#binlog_transaction_dependency_tracking = WRITESET
#slave_preserve_commit_order = 1
#slave_checkpoint_period = 2
#replication_optimize_for_static_plugin_config = ON
#replication_sender_observe_commit_only = ON
########mgr settings########
#loose-plugin_load_add = 'mysql_clone.so'
#loose-plugin_load_add = 'group_replication.so'
#loose-group_replication_group_name = "aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1"
#loose-group_replication_local_address = "192.168.1.51:33061"
#loose-group_replication_group_seeds = "192.168.1.51:33061,192.168.1.52:33061,192.168.1.53:33061"
#loose-group_replication_start_on_boot = OFF
#loose-group_replication_bootstrap_group = OFF
#loose-group_replication_exit_state_action = READ_ONLY
#loose-group_replication_flow_control_mode = "DISABLED"
#loose-group_replication_single_primary_mode = ON
#loose-group_replication_autorejoin_tries = 288
#loose-group_replication_member_expel_timeout = 5
#loose-group_replication_unreachable_majority_timeout = 30
#loose-group_replication_communication_max_message_size = 10M
########innodb settings########
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_buffer_pool_size = 500M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4000
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 32M
innodb_redo_log_capacity = 3G
innodb_max_undo_log_size = 4G
innodb_online_alter_log_max_size = 4G
innodb_print_ddl_logs = 1
innodb_status_file = 1
innodb_status_output = 0
innodb_status_output_locks = 1
innodb_stats_persistent_sample_pages = 500
innodb_adaptive_hash_index = 0
sql_generate_invisible_primary_key = ON
innodb_flush_neighbors = 1
innodb_purge_threads = 4
#innodb_large_prefix = 1
innodb_thread_concurrency = 64
innodb_strict_mode = 1
innodb_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=1
innodb_autoextend_increment=64
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=65536
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
innodb_data_file_path=ibdata1:200M;ibdata2:200M;ibdata3:200M:autoextend:max:5G
innodb_temp_data_file_path = ibtmp1:200M:autoextend:max:20G
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_purge_rseg_truncate_frequency = 128
#transaction_write_set_extraction=MURMUR32
#default_authentication_plugin=mysql_native_password
#default_authentication_plugin=caching_sha2_password
#innodb monitor settings
innodb_monitor_enable = "module_innodb"
innodb_monitor_enable = "module_server"
innodb_monitor_enable = "module_dml"
innodb_monitor_enable = "module_ddl"
innodb_monitor_enable = "module_trx"
innodb_monitor_enable = "module_os"
innodb_monitor_enable = "module_purge"
innodb_monitor_enable = "module_log"
innodb_monitor_enable = "module_lock"
innodb_monitor_enable = "module_buffer"
innodb_monitor_enable = "module_index"
innodb_monitor_enable = "module_ibuf_system"
innodb_monitor_enable = "module_buffer_page"
#innodb_monitor_enable = "module_adaptive_hash"
#pfs settings
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'
[mysqldump]
quick
5-mysql 数据库初始化启动操作
5.1 配置 mysql 用户及组
## 创建规划数据目录
mkdir /mysql/{data,log}/3306 -p
## 创建系统组
groupadd mysql
## 创建系统用户
useradd -r -g mysql -s /bin/false mysql
## 授权
chown mysql:mysql -R /mysql
5.2 mysql 初始化操作
## 初始化操作
### MySQL5.7版本初始化操作
mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=/mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data
### MySQL8.*版本初始化操作
mysqld --defauls-file=/mysql/data/3306/my.cnf --initialize
6-mysql 启停脚本配置
6-1 Linux7及以上方式脚本配置
## Linux7系及更高版本，使用系统功能添加mysql启停管理
vim /etc/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/mysql/app/mysql/bin/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf
LimitNOFILE = 65535
## 添加权限
chmod +x /etc/systemd/system/mysqld.service
6-2 Linux6及以下方式配置脚本
cd /mysql/app/mysql/support-files/
cp -a mysql.server mysqld.server
# 关键内容修改如下
46 basedir=/mysql/app/mysql
47 datadir=/mysql/data/3306/data
63 mysqld_pid_file_path=/mysql/data/3306/mysql.pid
66 basedir=/mysql/app/mysql
67 bindir=/mysql/app/mysql/bin
70 datadir=/mysql/data/3306/data
72 sbindir=/mysql/app/mysql/bin
73 libexecdir=/mysql/app/mysql/bin
207 conf=/mysql/data/3306/my.cnf
266 $bindir/mysqld_safe --defaults-file=/mysql/data/3306/my.cnf --datadir="$datadir" --pid-file="$mysqld_pid_file_path" $other_args >/dev/null &
cp -a mysqld.server /etc/init.d/mysqld.server
6-3 mysql 启停命令
# 启动
systemctl start mysqld.service
# 状态
systemctl status mysqld.service
# 停止
systemctl stop mysqld.service
# 开机自启
systemctl enable mysqld.service
# 启动
service mysqld.server start
# 状态
service mysqld.server status
# 停止
service mysqld.server stop
# 开机自启
chkconfig --level 2345 mysqld.server on
chkconfig --list | grep mysql
6-4 mysql 首次登录及配置
# 查看默认初始密码
more /mysql/log/3306/[host]-error.error | grep "root@localhost"
# 首次登陆涉及修改默认
mysql -uroot -p
# mysql5.7版本修改密码方式
set password=password('rootroot');
flush privileges;
# mysql8*版本修改密码方式
alter user 'root'@'localhost' identified by 'rootroot';
flush privileges;
# 开启远程登陆授权
# 5.7版本操作
grant all privileges on *.* to 'root'@'%' identified by 'root'
6-5 测试数据操作
-- 创建测试数据
-- 创建数据库
create database itpux;
--创建用户
create user 'itpux'@'%' identified by 'itpux';
flush privileges;
grant all privileges on itpux.* to 'itpux'@'%' identified by 'itpux';
grant all privileges on itpux.* to 'itpux'@'localhost' identified by 'itpux';
select host,user from mysql.`user`;
--创建表
use itpux;
create table dept (
deptno int auto_increment primary key,
dname varchar(15),
loc varchar(50)
) engine = innodb;
--插入数据
insert into dept values (1,'it','bj');
insert into dept values (2,'cw','sh');
insert into dept values (3,'hr','sz');
commit;
--查询数据
select * from dept;
浙公网安备 33010602011771号