[数据库]Ubuntu Linux/Kylin: 安装MySQL_Ubuntu_其他_系统运维

1 文由

由于安装环境较为特殊，实在折煞人也。而此环境的网络博客/教程偏少，觉得有必要记录一下。

2 环境

安装主机不支持联网即不支持APT/APT-GET等傻瓜式的在线安装方式。

硬件架构: AARCH64(ARM64架构的V8状态)
OS: Kylin(国产操作系统:银河麒麟)
- 基于 Ubuntu Linux内核

root@Kylin:~# cat /proc/version
Linux version 4.15.0-72kord1-generic (root@Kylin) (gcc version 5.4.0 20160609 (Ubuntu/Linaro 5.4.0-6kord1~16.04.10)) #72kord1 SMP Tue Jan 14 20:23:52 CST 2020

root@Kylin:~# uname -a
Linux Kylin 4.15.0-72kord1-generic #72kord1 SMP Tue Jan 14 20:23:52 CST 2020 aarch64 aarch64 aarch64 GNU/Linux

3 安装过程

下载、上传: 支持ARM64的MySQL安装包到目标主机

https://launchpad.net/ubuntu/bionic/arm64/mysql-server
https://launchpad.net/ubuntu/bionic/arm64/mysql-server/5.7.27-0ubuntu0.18.04.1

安装: MySQL

dpkg -i mysql-server_5.7.27-0ubuntu0.18.04.1_all.deb

初次登陆: MySQL
登陆前，需配置MySQL的socket通信文件(mysqld.sock)

sudo mysql -u root -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

解决方法: 
+ 创建sock文件
      mkdir /var/run/mysqld/
      touch /var/run/mysqld/mysqld.sock
+ 修改文件所有者
      chown mysql /var/run/mysqld/mysqld.sock
+ 重启服务
      service mysql restart

查看用户(debian-sys-maint)初始登陆密码

cat /etc/mysql/debian.cnf
      user     = debian-sys-maint
      password = JALJKBhjpqCfgeIPyo

再次登陆

root@Kylin:~# mysql -udebian-sys-maint -pJALJKBhjpqCfgeIPyo

修改: 密码

show databases;
use mysql;
update user set authentication_string=PASSWORD("123456") where user='root';
update user set plugin="mysql_native_password";
flush privileges;
quit;
	
mysql -uroot -p      #输入上面查到的临时密码
alter user 'root'@'localhost' identified by '123456';
create user 'root'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

配置: 支持远端连接

root@Kylin:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
root@Kylin:~# mysql -u root -p
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    # bind-address            = 127.0.0.1
    bind-address              = 0.0.0.0

配置： mysql系统级配置
为什么用这种繁琐、效率低下的配置方式？
因为N次尝试直接配置mysqld.cnf文件后，再重启MySQL均失败，实在不得已而为之。

修改/etc/mysql/mysql.conf.d/mysqld.cnf(等同于配置其他常见主流教程中的/etc/mysql/my.cnf)配置：

查找 OS配置文件: find / -name "my.cnf"
查看 MySQL配置项： show variables like 'datadir';

    set global thread_cache_size=64;
    set global max_allowed_packet=104857600;
    	100M = 100*1024*1024 = 104857600
    set global key_buffer_size = 629145600;
    	600M = 600*1024*1024 = 629145600
    set global query_cache_size = 268435456;
    	256M = 256*1024*1024 = 268435456
    set global max_connections=500;
    set global table_open_cache = 1024;
        # table_cache 在5.1.3以后叫做 table_open_cache
    set global innodb_buffer_pool_size=2147483648;
    	2048M = 2048*1024*1024 = 2147483648
    set global collation_server=utf8_bin;
    set global sql_mode='NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES';
    set global sort_buffer_size = 6144;
    	6M = 6*1024= 6144
    set global read_buffer_size = 6144;
        6M = 6*1024= 6144
    set global read_rnd_buffer_size = 8192;
    	8M = 8*1024 = 8192
    set global myisam_sort_buffer_size = 65536;
		64M = 64*1024= 65536
    set global innodb_flush_log_at_trx_commit = 2;    
    
    [read only] 【只能通过配置文件修改，本次安装没有配置成功如下配置项】
    set global datadir='/var/lib/mysql/' 【有改动】
    set global symbolic-links=0;
    set global log_error='/var/log/mysql/error.log'; 【有改动】
    set global skip_name_resolve;
        # 能让MySQL登陆链接变快速      # 0 关闭支持符号链接
 	set global lower_case_table_names=0;
    set global innodb_log_file_size = 1073741824;
    	1024M = 1024*1024*1024 = 1073741824
    set global innodb_log_files_in_group = 3;

配置: MySQL端口

vi /etc/mysql/mysql.conf.d/mysqld.cnf
      [mysqld]
      port = 1314

配置: 字符集编码

【服务端字符集】
set global character_set_server=utf8; 
或(推荐↓)
vi /etc/mysql/mysql.conf.d/mysqld.cnf
      [mysqld]
      character-set-server=utf8

【数据库字符集】[可选]
set global character_set_database=utf8;

【客户端字符集】
vi /etc/mysql/conf.d/mysql.cnf 
      [mysql]
      no-auto-rehash
      default-character-set=utf8

查看 MySQL字符集编码: show variables like '%character%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

配置MySQL服务到系统服务中

systemctl start mysql.service

上述操作失败时，可进行的辅助操作↓

cat /var/log/syslog

service mysql restart
service mysql status

4 文献: 参考与推荐

查看更多关于【Ubuntu】的文章