部署思路
1 将basedir和datadir单独分开进行存储
2 设置单独的basedir类似/mysqlbase/mysql-8.0.x.和固定的mysqlhome 类似/home/mysql/mysqlbase
使用ln -s进行软链接,方便以后的升级直接修改/home/mysql/mysqlbase的链接即可。其他部署也类似。
ln -s 真实存在的文件 公共连接文件
ln -s $DATADIR/mysql.sockt /tmp/mysql.socket
3 初始化
4 添加自启动。
1 解压软件包
cd /usr/local & mkdir mysql
cd mysql
# 将mysql安装包放在该目录下,安装包在文档最下面
tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.32-linux-glibc2.12-x86_64 mysql8.0
cd mysql8.0
创建通用目录链接mysql8.0
ln -s /home/mysql/mysql8.0 /usr/local/mysql
2 创建mysql用户,赋予目录权限,修改环境变量
mkdir data
groupadd mysql
useradd -g mysql mysql
chown -R mysql.mysql /usr/local/mysql/mysql-8.0
chmod 750 /usr/local/mysql/mysql-8.0/data -R
vim /etc/profile
export PATH=$PATH:/usr/local/mysql/mysql-8.0/bin:/usr/local/mysql/mysql-8.0/lib
source /etc/profile
3 编写配置文件
配置文件中加入误操作避免符
设置prompt主要是方便搞清楚生产系统中登录的是哪个库,哪个用户,防止误操作。
常用选项如下,其它选项详见官方文档:
Option Description
\c A counter that increments for each statement you issue
\D The full current date
\d The default database
\h The server host
\m Minutes of the current time
\R The current time, in 24-hour military time (0–23)
\s Seconds of the current time
\U Your full user_name@host_name account name
\u Your user name
\_ A space
\ A space (a space follows the backslash)
\' Single quote
\" Double quote
\\ A literal \ backslash character
官方给出了共4种方式去设置:1).推荐,直接在mysql命令窗口中设置
mysql> prompt \R:\m:\s \U[\d]>
如果要取消提示,敲prompt命令即可
1
2
2).设置MYSQL_PS1环境变量shell> export MYSQL_PS1="(\u@\h) [\d]> "
1
3).启动mysql时,使用–prompt参数shell> mysql --prompt="(\u@\h) [\d]> "
1
4).prompt参数写入参数文件,比如/etc/my.cnf
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
————————————————cat > /etc/my.cnf << EOF
[mysql]
default-character-set=utf8mb4
[client]
#port=3306
socket=/var/lib/mysql/mysql.sock[mysqld]
#port=3306
#server-id=3306
user=mysql
general_log = 1
general_log_file= /var/log/mysql/mysql.log
socket=/var/lib/mysql/mysql.sock
basedir=/usr/local/mysql/mysql-8.0
datadir=/usr/local/mysql/mysql-8.0/data
log-bin=/usr/local/mysql/mysql-8.0/data/mysql-bin
innodb_data_home_dir=/usr/local/mysql/mysql-8.0/data
innodb_log_group_home_dir=/usr/local/mysql/mysql-8.0/data/
character-set-server=utf8mb4
lower_case_table_names=1 --在一次部署8.0.27过程中默认将data directory初始化为0导致报错。
autocommit=1
default_authentication_plugin=mysql_native_password
symbolic-links=0
# Disabling symbolic-links is recommended to prevent assorted security risks
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]
log-error=/usr/local/mysql/mysql-8.0/data/mysql.log
pid-file=/usr/local/mysql/mysql-8.0/data/mysql.pid#
# include all files from the config directory
EOF
标准配置:
[root@localhost ~]# more /etc/my.cnf
## my.cnf for MySQL 5.7/8.0
## autoor: yejr(yejinrong@zhishutang.com, http://imysql.com, QQ: 4700963)
[client]
port = 22306
socket = /data/mysqldata/mysql.sock[mysql]
prompt="\R:\m:\s [\d]> "
no-auto-rehash[mysqld]
user = eoms
port = 22306
basedir = /data/mysql/
datadir = /data/mysqldata/
socket = /data/mysqldata/mysql.sock
pid-file = mysqltest.pid
character-set-server = utf8mb4
skip_name_resolve = 1
#skip-grant-tables
lower_case_table_names=1#innodb_force_recovery = 6
#鑻ヤ綘鐨凪ySQL鏁版嵁搴撲富瑕佽繍琛屽湪澧冨锛岃鍔″繀鏍规嵁瀹為檯鎯呭喌璋冩暣鏈弬忙efault_time_zone = "+8:00"open_files_limit = 65535
back_log = 1024
max_connections = 2000
max_connect_errors = 1000000
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
log_timestamps = SYSTEM
slow_query_log_file = /data/mysqldata/slow.log
log-error = /data/mysqldata/error.log
long_query_time = 0.1
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
server-id = 22306
log-bin = /data/mysqldata/mybinlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 2G
max_binlog_size = 1G#娉ㄦ剰锛歁ySQL 8.0寮€濮嬶紝binlog_expire_logs_seconds閫夐」涔熷瓨鍦ㄧ殑璇濓紝浼氬拷鐣xpire_logs_days閫夐」
expire_logs_days = 7master_info_repository = TABLE
relay_log_info_repository = TABLE
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
binlog_format = row
binlog_checksum = 1
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
### myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
#innodb_sync_spin_loops = 100
#innodb_spin_wait_delay = 30transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 11469M
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:12M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
innodb_max_undo_log_size = 4G
innodb_undo_directory = /data/mysqldata
innodb_undo_tablespaces = 95# 鏍规嵁鎮ㄧ殑鏈嶅姟鍣↖OPS鑳藉姏閫傚綋璋冩暣
# 涓€鑸厤鏅€歋SD鐩樼殑璇濓紝鍙互璋冩暣氓0000 - 20000
# 閰嶇疆楂樼PCIe SSD鍗$殑璇濓紝鍒欏彲浠ヨ皟鏁寸殑鏇撮珮锛屾瘮氓0000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_undo_log_truncate = 1 # some var for MySQL 8
log_error_verbosity = 3
binlog_expire_logs_seconds = 604800
#innodb_dedicated_server = 0innodb_status_file = 1
#娉ㄦ剰: 寮€氓nnodb_status_output & innodb_status_output_locks 氓鍙兘浼氬鑷磍og-error鏂囦欢澧為暱杈冨揩
innodb_status_output = 0
innodb_status_output_locks = 1innodb_sort_buffer_size = 67108864
innodb_autoinc_lock_mode = 1#performance_schema
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
#performance_schema_instrument = '%lock%=on'#innodb monitor
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"[mysqldump]
quick
max_allowed_packet = 32M
[root@localhost ~]#
WINDOWS默认的ini文件配置
[mysql]
prompt="\R:\m:\s [\d]> "
no-auto-rehash
[mysqld]# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=F:\mysql\MySQL
gtid_mode=ON
log_bin=ON
log-slave-updates=ON
enforce-gtid-consistency# 设置mysql数据库的数据的存放目录
datadir=D:\mysql-8.0.24-winx64\data
basedir=D:\mysql-8.0.24-winx64
log-bin=D:\mysql-8.0.24-winx64\log# 允许最大连接数
max_connections=200
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8MB4
character-set-server=UTF8MB4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=UTF8MB4
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=UTF8MB4
4 初始化数据库
cd /usr/local/mysql/mysql-8.0/bin
./mysqld --user=mysql --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/mysql-8.0 --datadir=/usr/local/mysql//mysql-8.0/data/ --initialize --lower_case_table_names=1
fF%hk,jfg0eO # 记住这个初始登录密码
5 配置自动启动
复制 mysql.server 文件,在/usr/local/mysql/mysql-8.0目录下执行
cp -a ./support-files/mysql.server /etc/init.d/mysql
cp -a ./support-files/mysql.server /etc/init.d/mysqld
赋权chown 777 /etc/my.cnf
chmod +x /etc/init.d/mysql
chmod +x /etc/init.d/mysqldmkdir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql/
6 启动mysql,修改root密码
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
flush privileges;
修改远程登录连接create user 'root'@'%' identified by '远程连接密码';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
————————————————