Setup MariaDB on Ubuntu server (remote access, user privileges, upload database)

p>This is most spread operation in Linux and I repeat it again and again in various circumstances.

• Deploy ASP.NET Core application from scratch (install and tune SSH/VNC/FTP/NGINX/Daemon)
• Deploy data to dockerized MySQL and MariaDB
• Поднимаем на хостинге MySQL и PostgreSQL сервера.

Now I want to setup MariaDB, configure it to remote access, set up user privileges and upload database.

For check remote connection I will be use MySQL Workbench, unfortunately even installation it to Windows is a problem, because official distribution from Oracle don't working now https://dev.mysql.com/downloads/workbench/.

So, right link in this page other download.

Of course this way is not need if you want to connect to server only from Linux. In this case we need only:

# apt-get install mariadb-client
# mysql -u admin -h xxx.xxx.xxx.xxx -p

And we go ahead to server.

• This is official page to installation https://mariadb.com/docs/deploy/community-single-server-cs105-ubuntu18/.

  # wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
  # echo "7a24f5580421fd353dc22c5439001bdaec86c54ed911c80e5482f62921125ac8 mariadb_repo_setup" | sha256sum -c -
  # chmod +x mariadb_repo_setup
  # sudo ./mariadb_repo_setup --mariadb-server-version="mariadb-10.5"
  # sudo apt update
  # sudo apt install mariadb-server mariadb-backup
  

  
  
  
• Than need to start, this is my answer for server with remote access.

# sudo mysql_secure_installation





• And this is command to control MariaDB.

# sudo systemctl start mariadb
# sudo systemctl stop mariadb
# sudo systemctl restart mariadb
# sudo systemctl enable mariadb
# sudo systemctl disable mariadb
# sudo systemctl status mariadb





• This is a way to check current parameters of MariaDB

# mysqld --help --verbose





• This is a good time to first attempt to connect to server by client. If you select my way of secure installation sudo mariadb will be working without password, in other case need to set user and pass by this way.

  # sudo mariadb
  # sudo mysql -u root -p
  > select host, user, password from mysql.user;
  > show databases;
  > exit
  

  
  
  

  Alternative way to connect is:

  # mysqladmin -u root -p version
  

  
  
  
  
• Next step is change config - usually need to change port and in my case need to set remote access. This is location of config files in Ubuntu 16.

# sudo nano /etc/mysql/my.cnf

And this is support remote access to server.

[mysqld]
skip-networking=0
skip-bind-address





• For allow remote access we must open port in firewall. This is fast small help about UFW https://www.digitalocean.com/community/tutorials/how-to-set-up-a-firewall-with-ufw-on-ubuntu-18-04. In my case this is developer server and I opened it to everybody.



# sudo ufw allow 33306
# sudo ufw status numbered
# netstat -ant





• So. now this is a good time to going inside server and create users and new databases. In this case I created user Admin to fill access from any database and user cryptochest to allow access to only one database from the same host.



> create database cryptochest;
> GRANT ALL ON *.* TO 'admin'@'%' IDENTIFIED BY 'xxxxxxxxxxxxx' WITH GRANT OPTION;
> grant all privileges on cryptochest.* TO 'cryptochest'@'localhost' identified by 'yyyyyyyyyyyyyy';
> flush privileges;





• Next step is uploading data. I use this way.



> SET autocommit=0 ; source cryptochest-db-2020-11-30.sql ; COMMIT ;





• This is good time to connect to server remotely and check what we see in server. And also this is good time to change user privileges from client.




Unfortunately I see an error.





• Therefore need one additional step https://mariadb.com/kb/en/mysql_upgrade/

> mysql_upgrade --verbose --verbose --force --user=admin





• And eventually all working fine.

Save dump.

> sudo mysql -u root -p -h localhost
> use cryptochestmax;
> show tables;
> mysqldump -u cryptochest -p ---port=33306 cryptochestmax Coin > cryptochestmax.Coin.$(date +%Y-%m-%d-%H.%M.%S).sql

< THANKS ME>