Here are the commands to run to install MariaDB 10.2 from the MariaDB repository on your Ubuntu system:
sudo apt-get install software-properties-common
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xF1656F24C74CD1D8
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] http://mirrors.neusoft.edu.cn/mariadb/repo/10.2/ubuntu bionic main'
Once the key is imported and the repository added you can install MariaDB 10.2 from the MariaDB repository with:
sudo apt update
sudo apt install mariadb-server
Some times we need to stop or start MariaDB service. Use the following commands to stop, start, check status and restart MariaDB servic.
$ sudo systemctl stop mysql.service # To Stop MariaDB service
$ sudo systemctl start mysql.service # To Start MariaDB service
$ sudo systemctl status mysql.service # To Check MariaDB service status
$ sudo systemctl restart mysql.service # To Stop then Start MariaDB service
try remotely connect to the server
it's very trick,
firstly you have to comments bind-address in file /etc/mysql/my.cnf in case you are encountering 10061 error refer to this page
#bind-address = <some ip-address>
grant user to remote access the db via root user refer to this page for details
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;
and then reset root user of mariadb's password refer to this
MariaDB [(none)]> select host,user,password from mysql.user; +-----------+------+-------------------------------------------+
| host | user | password |
+-----------+------+-------------------------------------------+
| localhost | root | *C61A1C944BC7AXXXXXXXXXXXXXX4B81A462D9CB3 |
| 127.0.0.1 | root | *C61A1C944BC7A3XXXXXXXXXXXXX4B81A462D9CB3 |
| :: | root | *C61A1C944BC7A3XXXXXXXXXXXXX4B81A462D9CB3 |
| % | root | *81F5E21E35407DXXXXXXXXXXXXXEBFB6AF209E1B | wrong password made me facing 1045 error
+-----------+------+-------------------------------------------+
restar the db service
service mysql restart
if it's Centos 7, please use the following instead
systemctl restart mariadb.service
then connect to the db via Navicat or mariadb CLI command
mysql -h xxx.xx.xx -u root -p
others useful command
change user password
Open the bash shell and connect to the server as root user
mysql -u root -h localhost -p
Run command:
ALTER USER admin IDENTIFIED BY 'New-Password-Here';
change databse or table charset
ALTER DATABASE databasename CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE cen_bridge_dictionary CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
create database with specified charset
CREATE DATABASE IF NOT EXISTS RUNOOB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
backup and restore
backup db
mysqldump -u user_name -p --databases database_name >dump.sql
restore db
mysql -u root -p[root_password] --databases database_name < dumpfilename.sql
refer to this page for charactar set
reference documents
https://askubuntu.com/questions/1009175/mariadb-10-0-33-configuring-mariadb-for-remote-client-access
https://www.jb51.net/article/26505.htm
https://mariadb.com/kb/en/library/configuring-mariadb-for-remote-client-access/
https://downloads.mariadb.org/mariadb/repositories/#mirror=neusoft&distro=Ubuntu&distro_release=bionic--ubuntu_bionic&version=10.2
https://tecadmin.net/install-mariadb-10-on-ubuntu/