Shulou(Shulou.com)06/01 Report--

What is mariadb?

MariaDB database management system is a branch of MySQL, mainly by the open source community in the

Maintenance, the purpose of licensing MariaDB with GPL license is to be fully compatible with MySQL, including API and

The command line makes it an easy substitute for MySQL. On the storage engine side, use XtraDB

(English: XtraDB) to replace the InnoDB of MySQL. MariaDB is created by MySQL

Founder Michael Widenius (English: Michael Widenius) leads the development, he is early

The former sold the company he founded, MySQL AB, to SUN for $1 billion, and since then

With the acquisition of SUN by Oracle, the ownership of MySQL fell into the hands of Oracle. MariaDB

The name comes from the name of Michael Widenius's daughter Maria.

1.Mariadb installation

1-1 install mariadb and mariadb-client components:

# yum groupinstall-y mariadb mariadb-client

1-2 start the mariadb service:

# systemctl start mariadb; systemctl enable mariadb

[root@server1 ~] # ss-antple | grep mysql

LISTEN 0 50 *: 3306 *: *

Users: (("mysqld", 2622, 13)) uid:27 ino:36479 sk:ffff8800235a0000

1-4 Edit the / etc/my.cnf file and add the following parameters to [mysqld]:

Skip-networking=1

1-inch systemctl restart mariadb

# ss-antlp | grep mysql only allows local connections through socket files at this time, blocking

All tcp/ip connections from the network.

two。 Use the mysql_secure_installation tool for database security settings, and follow the prompts to complete

Action:

# mysql_secure_installation

[root@server1 ~] # mysql_secure_installation

/ usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current

Password for the root user. If you've just installed MariaDB, and

You haven't set the root password yet, the password will be blank

So you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB

Root user without the proper authorisation.

Set root password? [Y/n]

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone

To log into MariaDB without having to have a user account created for

Them. This is intended only for testing, and to make the installation

Go a bit smoother. You should remove them before moving into a

Production environment.

Remove anonymous users? [Y/n]

... Success!

Normally, root should only be allowed to connect from 'localhost'. This

Ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]

... Success!

By default, MariaDB comes with a database named 'test' that anyone can

Access. This is also intended only for testing, and should be removed

Before moving into a production environment.

Remove test database and access to it? [Y/n]

-Dropping test database...

... Success!

-Removing privileges on test database...

... Success!

Reloading the privilege tables will ensure that all changes made so far

Will take effect immediately.

Reload privilege tables now? [Y/n]

... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB

Installation should now be secure.

Thanks for using MariaDB!

[root@server1 ~] #

3. Log in to the database:

# mysql-u root-pEnter password: redhatMariaDB [(none)] > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | +-+ 3 rows in set (0.00 sec) MariaDB [(none)] > quit

4. Basic database operation SQL

Show databases; display database use mysql; enter the database show tables; display tables in the database desc user; view the data structure of the user table flush privileges; refresh database information select host.user,password from user Query host,user,password field create database westos; in user table to create westos database use westos; create table linux (create table, username,password field username varchar (15) not null,password varchar (15) not null); select * from mysql.user Query all alter table linux add age varchar (4) in the user table under the mysql library; add the age field to the linux table alter table linux drop age;alter table linux add age varchar (4) after username;show tables;desc linux; insert into linux values ('user1','passwd1'); insert the values username=user1, password= password1update linux set password=password (' passwd2') where username=user1 in the linux table Update the password of user1 in linux table to password2delete from linux where username=user1; to delete all contents of user1 in linux table

5.mysql password recovery

1) # systemctl stop mariadb

2) # mysql_safe-- skip-grant &

3) # mysql

Update mysql.user set password=password ('westos') where user='root'; updates the mysql.user table if the root user's password is encrypted westos 4) killall-9 mysqld_safe ps-aux | grep mysql kill-9 * 5) systemctl start mariadb

6. Users and access permissions

Create user CREATE USER wxh@localhost identified by 'westos';CREATE USER lee@'%' identified by' redhat'; user Authorization GRANT INSERT,UPDATE,DELETE,SELECT on mariadb.* to wxh@localhost;GRANT SELECT on mariadb.* lee@'%'; overload Authorization Table FLUSH PRIVILEGES; View user Authorization SHOW GRANTS FOR wxh@localhost; revoke user Rights REVOKE DELETE,UPDATE,INSERT on mariadb.* from wxh@localhost; remove user DROP USER wxh@localhost

7. Backup and recovery

Backup # mysqldump-uroot-predhat westos > westos.dump# mysqldump-uroot-predhat-- all-databases > backup.dump# mysqldump-uroot-predhat-- no-data westos > westos.dump# # only backup framework, no data backup. Restore # mysqladmin-uroot-predhat create db2 or mysql-uroot-predhat-e 'CREATE DATABASE db2;'# mysql-uroot-predhat db2

< westos.dump 8.网页管理数据库 1)装软件http,php,php-mysql (phpMyAdmin-3.4.0-all-languages)2)cd /var/www/html3)下载解压php压缩包,4)mv phpMyAdmin-3.4.0-all-languages/ myadmin 5)cd myadmin/6)cp config.sample.inc.php config.inc.php vim config.inc.php $cfg['blowfish_secret'] = ''; ==>

$cfg ['blowfish_secret'] =' steven'; 7) systemctl restart httpd 8) 172.252.254.X/myadmin

Database backup script

#! / bin/bashHELLO=$ 1.`date +% Ymuri% mmury% d`.sqlread-p "please input your user name:" NAMEread-s-p "please input the user password:" PASSWORDmkdir / mydata & > / dev/nulltouch / mydata/$HELLOmysqldump-u$NAME-p$PASSWORD $1 > / mydata/$HELLOecho-e "\ nThe backup successful!"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.