Ubuntu22.04 MySQL8.0.3+安装

Vivant

# 更新apt

1. apt -y update

复制代码

# 安装mysql-server

1. apt -y install mysql-server

复制代码

# 查看服务是否启动

1. ps -ef | grep mysql
   
2. systemctl status mysql

复制代码

# 设置忽略表名大小写
## 停止服务

1. sudo systemctl stop mysql.service

复制代码

## 删除错误日志

1. sudo rm /var/log/mysqld.log

复制代码

## 删除系统数据库与用户数据库

1. sudo rm -rf /var/lib/mysql

复制代码

## 创建数据库目录

1. sudo mkdir /data
   
2. sudo mkdir /data/mysql
   
3. sudo mkdir /data/mysql/tmp
   
4. sudo mkdir /data/mysql/logs
   
5. sudo mkdir /data/mysql/binlogs
   
6. sudo mkdir /data/mysql/relaylogs
   
7. sudo mkdir /data/mysql/undologs

复制代码

## 目录授权

1. sudo chown -R mysql:mysql /data
   
2. sudo chown -R mysql:mysql /data/mysql
   
3. sudo chown -R mysql:mysql /data/mysql/tmp
   
4. sudo chown -R mysql:mysql /data/mysql/logs
   
5. sudo chown -R mysql:mysql /data/mysql/binlogs
   
6. sudo chown -R mysql:mysql /data/mysql/relaylogs
   
7. sudo chown -R mysql:mysql /data/mysql/undologs

复制代码

## 配置 lower_case_table_names

1. sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

复制代码

1. # For advice on how to change settings please seehttp://dev.mysql.com/doc/refman/ ... ation-defaults.html
   
2. 
   
3. [mysqld]
   
4. #Remove leading # and set to the amount of RAM for the most important datacache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
   
5. 
   
6. #innodb_buffer_pool_size = 128M
   
7. 
   
8. #Remove the leading "# " to disable binary logging
   
9. 
   
10. #Binary logging captures changes between backups and is enabled by
    
11. 
    
12. #default. It's default setting is log_bin=binlog
    
13. 
    
14. #disable_log_bin
    
15. 
    
16. #Remove leading # to set options mainly useful for reporting servers.
    
17. 
    
18. #The server defaults are faster for transactions and fast SELECTs.
    
19. 
    
20. #Adjust sizes as needed, experiment to find the optimal values.
    
21. 
    
22. #join_buffer_size = 128M
    
23. 
    
24. #sort_buffer_size = 2M
    
25. 
    
26. #read_rnd_buffer_size = 2M
    
27. 
    
28. #Remove leading # to revert to previous value for default_authentication_plugin,
    
29. 
    
30. #this will increase compatibility with older clients. For background, see:https://dev.mysql.com/doc/refman ... thentication_plugin
    
31. 
    
32. #default-authentication-plugin=mysql_native_password
    
33. 
    
34. #datadir=/var/lib/mysql
    
35. #socket=/var/lib/mysql/mysql.sock
    
36. 
    
37. #log-error=/var/log/mysqld.log
    
38. #pid-file=/var/run/mysqld/mysqld.pid
    
39. 
    
40. socket = /data/mysql/tmp/mysql.sock
    
41. pid-file = /data/mysql/tmp/mysqld.pid
    
42. log_error = /data/mysql/logs/mysqld.log
    
43. log_error_verbosity = 3
    
44. slow_query_log = 0
    
45. slow_query_log_file = /data/mysql/logs/slow.log
    
46. long_query_time = 5
    
47. datadir = /data/mysql/data
    
48. 
    
49. user = mysql
    
50. port = 3306
    
51. server-id = 92
    
52. max_connections=9000
    
53. open_files_limit = 65535
    
54. default_authentication_plugin=mysql_native_password
    
55. max_allowed_packet = 1G
    
56. max_connect_errors = 1000000
    
57. character_set_server=utf8mb3
    
58. default_time_zone='+8:00'
    
59. transaction_isolation = READ-COMMITTED
    
60. lower-case-table-names=1
    
61. mysqlx=0   
    
62. skip_name_resolve
    
63. 
    
64. #enable admin_port
    
65. admin_address = '127.0.0.1'
    
66. admin_port = 33066
    
67. 
    
68. #pfs settings
    
69. performance_schema = 1
    
70. performance_schema_instrument = '%lock%=on'
    
71. 
    
72. #performance setttings
    
73. lock_wait_timeout = 3600
    
74. back_log = 1024
    
75. table_open_cache = 19024
    
76. table_definition_cache = 19024
    
77. thread_stack = 512K
    
78. sort_buffer_size = 16M
    
79. join_buffer_size = 16M
    
80. read_buffer_size = 8M
    
81. read_rnd_buffer_size = 16M
    
82. bulk_insert_buffer_size = 64M
    
83. thread_cache_size = 3000
    
84. #interactive_timeout = 600
    
85. #wait_timeout = 600
    
86. tmp_table_size = 96M
    
87. max_heap_table_size = 96M
    
88. max_prepared_stmt_count=128000
    
89. 
    
90. #log settings
    
91. binlog_format = ROW
    
92. log-bin = /data/mysql/binlogs/mysql-bin
    
93. relay-log = /data/mysql/relaylogs/relay-bin
    
94. log_timestamps = SYSTEM
    
95. sync_binlog = 1
    
96. binlog_cache_size = 128M
    
97. binlog_rows_query_log_events = 1
    
98. binlog_expire_logs_seconds = 180000
    
99. binlog_checksum = CRC32
    
100. gtid_mode = ON
     
101. enforce_gtid_consistency = TRUE
     
102. 
     
103. #undo settings
     
104. innodb_undo_directory = /data/mysql/undologs/
     
105. innodb_undo_tablespaces=3
     
106. innodb_undo_log_truncate=1
     
107. innodb_max_undo_log_size = 4G
     
108. innodb_purge_rseg_truncate_frequency = 128
     
109. 
     
110. #innodb
     
111. default-storage-engine = InnoDB   
     
112. innodb_data_file_path = ibdata1:1024M;ibdata2:1024M;ibdata3:1024M:autoextend  
     
113. innodb_flush_neighbors = 0   
     
114. innodb_doublewrite= 1
     
115. innodb_log_file_size = 1G
     
116. innodb_log_files_in_group = 16
     
117. innodb_log_buffer_size = 64M
     
118. innodb_flush_log_at_trx_commit = 2
     
119. innodb_thread_concurrency = 8
     
120. innodb_buffer_pool_size = 40G
     
121. innodb_buffer_pool_instances = 4
     
122. innodb_open_files = 65535
     
123. innodb_file_per_table = 1
     
124. innodb_strict_mode = 0
     
125. innodb_io_capacity = 10000
     
126. innodb_io_capacity_max = 20000
     
127. innodb_flush_method = O_DIRECT
     
128. innodb_adaptive_hash_index = OFF
     
129. innodb_lru_scan_depth = 4000
     
130. innodb_lock_wait_timeout = 10
     
131. innodb_rollback_on_timeout = 1
     
132. innodb_print_all_deadlocks = 1
     
133. innodb_online_alter_log_max_size = 4G
     
134. innodb_sort_buffer_size = 67108864
     
135. innodb_status_output = 0
     
136. innodb_adaptive_hash_index = OFF
     
137. 
     
138. [mysqldump]
     
139. quick
     
140. max_allowed_packet = 128M
     
141. 
     
142. [client]
     
143. port        = 3306
     
144. socket        = /data/mysql/tmp/mysql.sock
     
145. default_character-set=utf8mb3

复制代码

# 禁用apparmor profile

1. ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
   
2. apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld
   

复制代码

## 重启 apparmor

1. systemctl restart apparmor

复制代码

## 查询mysql，应当无结果

1. aa-status |grep mysql

复制代码

# 初始化 MySql

1. mysqld --defaults-file=/etc/mysql/my.cnf --initialize  --user=mysql --basedir=/data/mysql --datadir=/data/mysql/data

复制代码

## 登录 MySql

1. sudo cat  /data/mysql/logs/mysqld.log  | grep password

复制代码

## 使用临时密码登录 MySql，设置root登录密码

1. mysql -uroot
   
2. use mysql;
   
3. alter user 'root'@'localhost' identified by 'Aa123456';
   
4. update user set host='%' where user='root';
   
5. flush privileges;

复制代码

## 查看当前 mysql 是否是区分大小写

1. show variables like '%case_table%';
   
2. exit;

复制代码

## 重启服务

1. service mysql restart

复制代码

## 查看服务是否启动

1. ps -ef | grep mysql
   
2. systemctl status mysql

复制代码