Skip to main content

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

![Navigation bar](../../images/banner/nav-hint-banner.png)

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
AI Translation Notice

This page was translated into English by AI and is provided for reference only. The original document is written in Chinese. If there is any discrepancy, the Chinese version prevails.

本页英文内容由 AI 自动翻译生成,仅供参考;原文为中文,如有差异请以中文原文为准。

查看中文原文 / View the Chinese original →