MariaDB二进制包安装
<p>systemd包安装
<a href="https://www.jianshu.com/p/9559670faf74">https://www.jianshu.com/p/9559670faf74</a>
<a href="https://blog.csdn.net/ncyind/article/details/107128235">https://blog.csdn.net/ncyind/article/details/107128235</a></p>
<h3>1、官网下载二进制包并上传至/opt目录下</h3>
<p><a href="https://downloads.mariadb.org/">https://downloads.mariadb.org/</a>
mariadb-10.5.1-linux-systemd-x86_64.tar.gz</p>
<h3>2、准备工作</h3>
<p>安装依赖包，否则msyql -p 登陆会报错。</p>
<pre><code class="language-bash">#CentOS8
dnf -y install libncurses*
#CentOS7默认最小化安装已经安装此包
##useradd -s /sbin/nologin -M mysql #不需执行</code></pre>
<h3>3.创建mysql用户和组</h3>
<pre><code class="language-bash">groupadd mysql
useradd mysql -g mysql -d /usr/local/mysql -s /sbin/nologin</code></pre>
<pre><code class="language-bash">tar zxvf /opt/mariadb-10.5.1-linux-systemd-x86_64.tar.gz -C /usr/local
mv /usr/local/mariadb-10.5.1-linux-systemd-x86_64/* /usr/local/mysql/
rmdir /usr/local/mariadb-10.5.1-linux-systemd-x86_64
chown -R mysql:mysql /usr/local/mysql
mkdir /data
chown -R mysql:mysql /data/</code></pre>
<h3>4、初始化数据库</h3>
<pre><code class="language-bash">/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/data</code></pre>
<h3>5、配置启动脚本</h3>
<pre><code class="language-bash">cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
vim /etc/init.d/mysqld
basedir=/usr/local/mysql
datadir=/data/mysql/data</code></pre>
<h3>6、配置环境变量</h3>
<pre><code class="language-bash">echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh</code></pre>
<h3>7、修改配置文件</h3>
<p>vim /etc/my.cnf</p>
<pre><code class="language-bash">[client]
port = 3306
default-character-set=utf8mb4
socket = /tmp/mysql.sock
[mysqld]
character-set-server = utf8mb4
port = 3306
socket = /tmp/mysql.sock
datadir = /data
basedir = /usr/local/mysql
# innodb_fle_per_table = on
skip_name_resolve = on
slow-query-log= 1
long_query_time = 2
lower_case_table_names = 1
innodb_log_file_size = 512M
default-time_zone = '+8:00'
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800
back_log = 1024
event_scheduler = OFF
open_files_limit = 65535
</code></pre>
<p><del>new,目录分得细一点，还未验证在整个文档中是否有效。</del></p>
<p><del>[client]</del>
<del>port = 3306</del>
<del>socket = /data/mysql/socket/mysqld.sock</del>
<del>default-character-set = utf8mb4</del></p>
<p><del>[mysqld]</del>
~~basedir = /usr/local/mysql/
datadir = /data/mysql/data
tmpdir = /data/mysql/tmp
socket = /data/mysql/socket/mysqld.sock
log_bin = /data/mysql/log/mysql-bin.log
pid-file = /data/mysql/socket/mysql.pid
character-set-server = utf8mb4
skip_name_resolve = on
slow-query-log= 1
long_query_time = 2
lower_case_table_names = 1
innodb_log_file_size = 512M
default-time_zone = '+8:00'
connect_timeout = 10
wait_timeout = 28800
interactive_timeout = 28800~~
~~back_log = 1024
event_scheduler = OFF~~
<del>open_files_limit = 65535</del></p>
<h3>8、启动服务</h3>
<pre><code class="language-bash">/etc/init.d/mysqld start</code></pre>
<h3>9.随机启动设置</h3>
<pre><code class="language-bash">chkconfig --add mysqld
chkconfig mysqld on</code></pre>
<h3>10.运行安全初始化脚本</h3>
<p>运行安全初始化脚本，设置root口令、禁用匿名登陆、禁用远程主机登陆、删除test数据库，并立即生效(根据提示操作)。</p>
<pre><code class="language-bash">/usr/local/mysql/bin/mysql_secure_installation</code></pre>
<h4>11、配置允许root远程登录</h4>
<p><a href="https://www.cnblogs.com/goxcheer/p/8797377.html">https://www.cnblogs.com/goxcheer/p/8797377.html</a></p>
<p>MySql-Server 出于安全方面考虑默认只允许本机(localhost, 127.0.0.1)来连接访问.</p>
<pre><code class="language-bash">[root@localhost etc]# mysql -u root -p</code></pre>
<p>连接后，操作mysql数据库</p>
<pre><code class="language-bash">MariaDB [mysql]> use mysql；</code></pre>
<p>查询用户命令</p>
<pre><code class="language-bash">MariaDB [mysql]> select User,authentication_string,Host from user;
+-------------+-----------------------+-----------+
| User | authentication_string | Host |
+-------------+-----------------------+-----------+
| mariadb.sys | | localhost |
| root | invalid | localhost |
| mysql | invalid | localhost |
+-------------+-----------------------+-----------+</code></pre>
<p>这里也可以看出host默认都是localhost访问权限
接下来就是最重要的部分了：</p>
<pre><code class="language-bash">MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456';</code></pre>
<p>这里的123456为你给新增权限用户设置的密码，%代表所有主机，也可以具体到你的主机ip地址</p>
<pre><code class="language-bash">MariaDB [mysql]> flush privileges;</code></pre>
<p>这一步一定要做，不然无法成功！ 这句表示从mysql数据库的grant表中重新加载权限数据
因为MySQL把权限都放在了cache中，所以在做完更改后需要重新加载。
执行完这两步，再次查询用户表命令：</p>
<pre><code class="language-bash">MariaDB [mysql]> select User,authentication_string,Host from user;
+-------------+-------------------------------------------+-----------+
| User | authentication_string | Host |
+-------------+-------------------------------------------+-----------+
| mariadb.sys | | localhost |
| root | invalid | localhost |
| mysql | invalid | localhost |
| root | *4F9D3C4DF16AA04558189C507A79CEA7DB63C2B5 | % |
+-------------+-------------------------------------------+-----------+</code></pre>
<p>发现多了一个用户，该用户所有的主机都可以访问，此时再次用sqlyog访问连接成功！</p>
<h4>12、修改root密码</h4>
<pre><code class="language-bash">alter user 'root'@'localhost' identified by 'newpasswd';</code></pre>
<h4>13、修改字符集</h4>
<p>参考 第7步,修改后查看</p>
<pre><code class="language-bash">MariaDB [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)</code></pre>
<p>使用navicat for mysql时，在Navicat中右键 数据库→连接属性→高级，将编码选为自动即可！
navicat连接成功后，在navicat中执行</p>
<pre><code class="language-bash">show variables like '%char%';</code></pre>
<pre><code class="language-bash">Variable_name Value
------------------------ -----------------------------------
character_set_client utf8
character_set_connection utf8
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8
character_set_server utf8mb4
character_set_system utf8
character_sets_dir /home/mysql/mysql/share/charsets/ </code></pre>
<p>看结果是否相同。</p>
<h4>14 Linux本地执行mysql脚本</h4>
<pre><code class="language-bash">mysql -uroot -phz310012 < EasyTong_MySQL_01_CreateDataBase.sql
mysql -uroot -phz310012 easytongdb < ./EasyTong_MySQL_02_CreateTable.sql
......</code></pre>
<p>说明：
如果使用 -h IP 参数，需在mysql服务端添加客户端IP；
登录mysql数据库执行</p>
<pre><code class="language-bash">source /opt/etdbscripts/EasyTong_MySQL_02_CreateTable.sql;</code></pre>