Ubuntu 离线安装mariadb 10.4及mysql初始化

Ubuntu 离线安装mariadb 10.4

准备工作

需要一台能连互联网的ubuntu电脑
操作系统版本与离线电脑版本一致，本文使用的是ubuntu18.04-server版本

联网电脑操作

安装dpkg-dev，后面要用到dpkg-scanpackages命令

apt-get install dpkg-dev

下载安装包，首先清空/var/cache/apt/archives目录，这里选择备份，防止文件丢失
2.1 安装基础组件

# 备份archives
mv /var/cache/apt/archives /var/cache/apt/archives-bak
# 新建一个archives目录
mkdir /var/cache/apt/archives
# 新建一个文件夹，后面用于拷贝
mkdir /home/dorahou/mariadb/
# 安装mariadb，此处不是真的安装，而是下载所有用到的离线包，-d表示只下载，是必加的
# 如果是在线安装，则不需要加-d
apt-get -d install software-properties-common
apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'

2.2 安装指定版本数据库，mariadb10.4版本，所以要修改源

vi /etc/apt/sources.list

在sources.list第一行添加如下内容，注意保留其他在线链接

deb [arch=amd64] https://mirrors.ustc.edu.cn/mariadb/repo/10.4/ubuntu bionic main

2.3 下载安装包

apt-get update
apt-get -d install mariadb-server mariadb-client

2.4 离线装包打包

# 复制archives文件夹，并用dpkg-scanpackages建Packages.gz索引
cp -r /var/cache/apt/archives /home/dorahou/mariadb/
cd /home/dorahou/mariadb/
dpkg-scanpackages archives /dev/null | gzip > archives/Packages.gz
# 打包，并将your_software.tar.gz拷贝到离线电脑或服务器上
cd ..
tar czvf mariadb.tar.gz mariadb

离线电脑操作

将mariadb.tar.gz拷贝到电脑或服务器上，并解压，也可以直接下载我已制作的包mariadb离线安装包

cd /home/dorahou/
tar xzvf mariadb.tar.gz
pwd

创建本地源
备份原始文件

# 备份源文件
mv  /etc/apt/sources.list  /etc/apt/sources.list-bak

编辑sources.list，在里面添加

# 编辑sources.list，在里面添加
deb [trusted=yes] file:/home/dorahou/mariadb archives/

安装mariadb-server

# 安装 mariadb
apt-get update
apt-get install mariadb-server
systemctl start mariadb

mysql 初始化

mariadb -v
mysql_secure_installation

参考

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

数据库使用

mysql -u root -p
# 修改端口，取消本地绑定
vi /etc/mysql/my.cnf

my.cnf参考文档

# MariaDB database server configuration file.
#
# You can copy this file to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
# add by dorahou
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
symbolic-links=0

lower_case_table_names=1
#max_allowed_packet=30M
slow_query_log=on
slow_query_log_file=/home/dorahou/mysql_data/slow_query_log.log
long_query_time=2


#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 33066
basedir		= /usr
datadir		= /home/dorahou/mysql_data/mysql
tmpdir		= /tmp
lc_messages_dir	= /usr/share/mysql
lc_messages	= en_US
skip-external-locking
#
# 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
#
# * Fine Tuning
#
max_connections		= 100
connect_timeout		= 5
wait_timeout		= 600
max_allowed_packet	= 30M
thread_cache_size       = 128
sort_buffer_size	= 4M
bulk_insert_buffer_size	= 16M
tmp_table_size		= 32M
max_heap_table_size	= 32M
#
# * MyISAM
#
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched. On error, make copy and try a repair.
myisam_recover_options = BACKUP
key_buffer_size		= 128M
#open-files-limit	= 2000
table_open_cache	= 400
myisam_sort_buffer_size	= 512M
concurrent_insert	= 2
read_buffer_size	= 2M
read_rnd_buffer_size	= 1M
#
# * Query Cache Configuration
#
# Cache only tiny result sets, so we can fit more in the query cache.
query_cache_limit		= 128K
query_cache_size		= 64M
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type		= DEMAND
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# we do want to know about network errors and such
log_warnings		= 2
#
# Enable the slow query log to see queries with especially long duration
#slow_query_log[={0|1}]
#slow_query_log_file	= /var/log/mysql/mariadb-slow.log
#long_query_time = 10
#log_slow_rate_limit	= 1000
log_slow_verbosity	= query_plan

#log-queries-not-using-indexes
#log_slow_admin_statements
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#report_host		= master1
#auto_increment_increment = 2
#auto_increment_offset	= 1
log_bin			= /var/log/mysql/mariadb-bin
log_bin_index		= /var/log/mysql/mariadb-bin.index
# not fab for performance, but safer
#sync_binlog		= 1
expire_logs_days	= 10
max_binlog_size         = 100M
# slaves
#relay_log		= /var/log/mysql/relay-bin
#relay_log_index	= /var/log/mysql/relay-bin.index
#relay_log_info_file	= /var/log/mysql/relay-bin.info
#log_slave_updates
#read_only
#
# If applications support it, this stricter sql_mode prevents some
# mistakes like inserting invalid dates etc.
#sql_mode		= NO_ENGINE_SUBSTITUTION,TRADITIONAL
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine	= InnoDB
innodb_buffer_pool_size	= 256M
innodb_log_buffer_size	= 8M
innodb_file_per_table	= 1
innodb_open_files	= 400
innodb_io_capacity	= 400
innodb_flush_method	= O_DIRECT
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completion
default-character-set=utf8

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!include /etc/mysql/mariadb.cnf
!includedir /etc/mysql/conf.d/

创建数据库

systemctl restart mariadb
mysql -u root -P 33066 -p
set PASSWORD=PASSWORD("123456");
# 退出后重新登录
# 依次执行以下命令建立数据库、用户、授权
create database dorahou_db character set utf8;
GRANT USAGE ON *.* TO 'dorahou'@'%' IDENTIFIED BY 'your_password'  WITH GRANT OPTION;
GRANT all privileges on smartvi .* to 'dorahou'@'%' identified by 'your_password';
flush privileges;

导入表结构
mysql -u dorahou -p dorahou_db< dorahou_db.sql

如有中文乱码问题可用如下命令查看
show variables like “%character%”;