mariadb-installation
1. Installation and Startup
apt install mariadb-server
- To allow external access, modify the configuration to permit it.
Edit vim /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 0.0.0.0
Restart Services
systemctl start mariadb
systemctl enable mariadb
## Initial configuration
mysql_secure_installation
# n yyyyy

2. Create User Test
mysql -u root
-- Change password: ROOT
set password for 'root'@'localhost' = Password('root');
-- !Important
FLUSH PRIVILEGES;
-- Create user
create user 'test'@'%' identified by 'test';
-- Modify user
rename user 'test'@'%' to 'test'@'%';
-- !Important
FLUSH PRIVILEGES;
-- Delete user
drop user 'test'@'%';
3. Create Database
DROP DATABASE test;
CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
3. Grant Privileges to User Test
CREATE DATABASE test;
-- View privileges
show grants for 'test'@'%';
-- Grant privileges: all privileges
grant create,insert,drop,delete,select,update,index,ALTER on test.* to 'test'@'%';
-- !Important
FLUSH PRIVILEGES;
-- Revoke privileges
revoke create,insert,drop,delete,select,update,index,ALTER on test.* from 'test'@'%';
4. Export Database
# Backup/Restore all databases
mysqldump -u root -p --all-databases > all-databases.sql
# Restore
mysql -u root -p < all-databases.sql
# Backup/Restore a single database
-- Backup
mysqldump -u root -p jpress > jpress.sql
mysqldump -u root -p wordpress > wordpress.sql
mysqldump -u root -p mc > mc.sql
mysqldump -u root -p test > test.sql
-- Restore: first create the database
mysql -u root -p -e "CREATE DATABASE jpress;"
mysql -u root -p jpress < jpress.sql
mysql -u root -p -e "CREATE DATABASE wordpress;"
mysql -u root -p wordpress < wordpress.sql
## Import via mysql shell
mysql -u root -p
mysql> use jpress
# Then use the source command to import the script file jpress.sql
mysql> source /opt/jpress.sql
select version();
+---------------------------+
| version() |
+---------------------------+
| 10.5.19-MariaDB-0+deb11u2 |
+---------------------------+
1 row in set (0.008 sec)
6. Reference Information
-- The following is copied from CSDN:
-- all privileges All permissions except GRANT
-- select Select permission only
-- select,insert Select and insert permissions
-- ...
-- usage No access permissions
-- alter Use alter table
-- alter routine Use alter procedure and drop procedure
-- create Use create table
-- create routine Use create procedure
-- create temporary tables Use create temporary tables
-- create user Use create user, drop user, rename user, and revoke all privileges
-- create view Use create view
-- delete Use delete
-- drop Use drop table
-- execute Use call and stored procedures
-- file Use select into outfile and load data infile
-- grant option Use grant and revoke
-- index Use index
-- insert Use insert
-- lock tables Use lock table
-- process Use show full processlist
-- select Use select
-- show databases Use show databases
-- show view Use show view
-- update Use update
-- reload Use FLUSH PRIVILEGES
-- shutdown Use mysqladmin shutdown (shut down MySQL)
-- super Use change master, kill, logs, purge, master, and set global. Also allows mysqladmin debug login?
-- replication client Access to server location information
-- replication slave Used by replication slaves
-- database_name.* All objects within a specific database
-- database_name.table A specific table within a database
-- database_name.procedure A specific stored procedure within a database
-- *.* All objects in all databases
-- Original link: https://blog.csdn.net/weixin_38091140/article/details/82983229
Complete Uninstall
systemctl stop mariadb
apt remove --purge mariadb-server mariadb-client mariadb-common
apt autoremove
rm -rf /etc/mysql/
rm -rf /var/lib/mysql/
rm -rf /var/log/mysql/
rm -rf /usr/share/mysql/
rm -rf /run/mysqld/
apt autoclean
apt clean
systemctl status mariadb
systemctl stop mariadb
apt remove --purge mariadb-server mariadb-client mariadb-common
apt remove mariadb-client-core
apt autoremove
rm -rf /var/lib/mysql/
rm -rf /etc/mysql/
rm -rf /run/mysqld/
deluser mysql 2>/dev/null || true