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

Case 1: authorized database user case 2: view and revoke authorization case 3: reset database management password case 4: remote maintenance database case 5: enterprise OA system deployment case 6: enterprise OA system migration

Case 1: authorizing database users

1.1 question

This example requires you to master the authorization operation of the user account in the MariaDB database and complete the following tasks:

1) set up a special library oadb for OA system, and authorize users

Allow user runoa to access the database locally and have full access to the library oadb. The password is pwd@123 test user runoa's database access.

2) create a new administrator named tarzan

Allow access from any client ('%') with full access to all libraries with the database access password of tedu.cn1234 test user tarzan

1.2 steps

To implement this case, you need to follow these steps.

Step 1: set up a special library oadb for the OA system and authorize users

1) create a database oadb

MariaDB [(none)] > CREATE DATABASE oadb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] >

2) the authorized user runoa accesses from the local machine and has full permissions to the library oadb. The access password is pwd@123.

MariaDB [(none)] > GRANT all ON oadb.* TO runoa@localhost IDENTIFIED BY 'pwd@123';Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] >

3) Test the database access permissions of runoa

Open another command line terminal to connect runoa users to the local database and test the deletion and reconstruction of the oadb library.

[root@zbx] # mysql-urunoa-ppwd@123.. .. MariaDB [(none)] > DROP DATABASE oadb;Query OK, 0 rows affected (0.01 sec) MariaDB [(none)] > CREATE DATABASE oadb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > QUIT;Bye [root@zbx ~] #

Step 2: create a new administrator named tarzan

1) add administrator users

Allow access from any other client ('%') with full access to all libraries with an access password of tedu.cn1234.

The administrator user has permission to authorize the user through GRANT.

MariaDB [(none)] > GRANT all ON *. * to tarzan@'%' IDENTIFIED BY 'tedu.cn1234' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] >

2) Test the database access rights by connecting with the new administrator user

When you use the mysql command to connect to the databases of other hosts, you need to add the-h host address option, such as accessing the MariaDB database at 192.168.10.7 from the client svr8.

[root@svr8] # mysql-utarzan-ptedu.cn1234-h292.168.10.7Welcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 2797Server version: 5.5.56-MariaDB MariaDB ServerCopyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] >

Test the new database zhdb:

MariaDB [(none)] > CREATE DATABASE zhdb;Query OK, 1 row affected (0.00 sec) MariaDB [(none)] >

Test the authorization database user zhwuji:

MariaDB [(none)] > GRANT all ON zhdb.* TO zhwuji@localhost IDENTIFIED BY 'pwd@123';Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] >

Exit:

MariaDB [(none)] > QUIT;Bye [root@zbx ~] #

Case 2: view and revoke authorization

2.1 question

This example requires learning to view and revoke the authorization of a specified user in the MariaDB database to complete the following tasks:

1) View the permissions of user tarzan to access the local database

2) revoke all permissions for user tarzan to access all libraries from any client

3) check the permissions of user tarzan to access the local database again

2.2 steps

To implement this case, you need to follow these steps.

Step 1: check the permissions of the user tarzan to access the local database

MariaDB [(none)] > SHOW GRANTS FOR tarzan@'%' +- -+ | Grants for tarzan@% | + -+ | GRANT ALL PRIVILEGES ON *. * TO 'tarzan'@'%' IDENTIFIED BY PASSWORD' * 8AB2CB3B8352A05A9C4AB822AAF421001382BD5E' WITH GRANT OPTION | +- - + 1 row in set (0.00 sec) MariaDB [(none)] >

Step 2: revoke all permissions for user tarzan to access all libraries from any client

MariaDB [(none)] > REVOKE all ON *. * FROM tarzan@'%';Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] >

Step 3: check the permissions of the user tarzan to access the local database again

MariaDB [(none)] > SHOW GRANTS FOR tarzan@'%' +- -- + | Grants for tarzan@% | + -- + | GRANT USAGE ON *. * TO 'tarzan'@'%' IDENTIFIED BY PASSWORD' * 8AB2CB3B8352A05A9C4AB822AAF421001382BD5E' WITH GRANT OPTION | +-- -+ 1 row in set (0.00 sec) MariaDB [(none)] >

3 case 3: reset database management password

3.1 question

This example requires learning to reset the basic methods of database administration passwords in order to gain administrative privileges if you forget or cannot learn the MariaDB database administration passwords, and complete the following tasks:

1) stop the mariadb service

2) Skip authorization to start the database process mysqld_safe

3) reset the administrative password

4) close the mysqld_safe process and start the mariadb service normally

5) verify the new password

3.2 steps

To implement this case, you need to follow these steps.

Step 1: stop the mariadb service

[root@svr7 ~] # systemctl stop mariadb / / stop service

Step 2: skip authorization to start the database process mysqld_safe

[root@svr7 ~] # mysqld_safe-- skip-grant-tables & / / straight up process [1] 105799 [root@svr7 ~] #

Step 3: reset the administrative password

1) password-free login to the database

[root@svr7] # mysql-uroot / / password-free login.. .. MariaDB [(none)] >

2) set a new password

MariaDB [(none)] > UPDATE mysql.user SET Password=password ('pwd@123') WHERE User='root' AND Host='localhost'; / / set new password Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0MariaDB [(none)] > FLUSH PRIVILEGES; / / Refresh Authorization Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] >

3) exit the connection

MariaDB [(none)] > QUIT / / exit Bye

Step 4: close the mysqld_safe process and start the mariadb service normally

1) close the mysqld_safe process

[root@svr7] # pkill-9 mysqld_safe / / Qiangguan mysqld_safe [1] + has killed mysqld_safe-- skip-grant-tables

2) start the mariadb service normally

[root@svr7 ~] # systemctl restart mariadb / / start normal service

Step 5: verify the new password

[root@svr7] # mysql-uroot-ppwd@123 / / verify the new password login.. .. MariaDB [(none)] > QUITBye [root@svr7 ~] #

4 case 4: remote maintenance of database

4.1 question

This example requires learning to remotely maintain the MariaDB database server system through MySQL-Front graphics software to complete the following tasks:

1) authorize administrative users on the MariaDB server

Allow root to access the machine from any IP address with a password of pwd@123

2) remotely manage MariaDB server from Win client

Install MySQL-Front management software and run MySQL-Front program, remotely connect to the MariaDB server to view the studb library stuinfo table data content backup studb library

4.2 steps

To implement this case, you need to follow these steps.

Step 1: authorize administrative users on the MariaDB server

Allow root to access the machine from any IP address with a password of pwd@123

[root@svr7] # mysql-uroot-ppwd@123.. .. MariaDB [(none)] > GRANT all ON *. * to root@'%' IDENTIFIED BY 'pwd@123' WITH GRANT OPTION;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > QUITBye [root@zbx ~] #

Step 2: remotely manage the MariaDB server from the Win client

1) install MySQL-Front management software

Double-click the installer MySQL-Front_Setup.exe, as shown in figure-1, and then follow the interface prompts to complete the installation

2) run MySQL-Front program and connect to MariaDB server remotely

Double-click the MySQL-Front shortcut icon on the desktop to open this software program. In the initial startup interface, you can fill in the database server information correctly according to the prompts, as shown in figure-2, and you can add a database connection after determination.

Then select the connection you just made, as shown in figure-3, and click Open.

Next, you will successfully connect to the target database server, as shown in figure-4, and all libraries that this user has permission to see are listed by default.

3) View the data contents of the stuinfo table in studb library

In the management interface of MySQL-Front connecting to the database server, you can operate on the specified libraries and tables.

For example, by expanding the studb library and selecting the stuinfo table, you can view or modify the structure of the table through the object browser on the right, as shown in figure-5.

Click the data browser on the right to view or modify the data contents of the table, as shown in figure-6.

4) back up studb library

In the MySQL-Front interface, right-click a library on the left and select "Export"-> "SQL File". You can choose the storage location and backup method, as shown in figure-7, and click "run" to perform the backup.

5 case 5: enterprise OA system deployment

5.1 question

This example requires the rapid deployment of the "letter-to-call Cooperative Office" system on the virtual machine 192.168.10.7 to prepare the environment for the next migration case and complete the following tasks:

1) prepare the LAMP environment and register the local domain name oa.tedu.cn

2) configure the virtual host oa.tedu.cn and use the "letter call cooperative office" code

3) prepare the dedicated database oadb and authorize the user runoa to have full access

4) adjust the attribution of the / var/www/oa directory so that the Web service has write permission

5) visit http://oa.tedu.cn/ and follow the prompts to install the OA system

5.2 steps

To implement this case, you need to follow these steps.

Step 1: prepare the LAMP environment and register the local domain name oa.tedu.cn

1) make sure that the LAMP website platform is launched

[root@svr7 ~] # yum-y install httpd mariadb-server maria php php-mysql [root@svr7 ~] # systemctl restart httpd mariadb [root@svr7 ~] # systemctl enable httpd mariadb

2) register the local domain name oa.tedu.cn

[root@svr7 ~] # vim / etc/hosts / / registers the local domain name.. .. 192.168.10.7 svr7.tedu.cn oa.tedu.cn

Step 2: configure the virtual host oa.tedu.cn and use the "letter call cooperative office" code

1) unpacking and deployment

[root@svr7 ~] # unzip / root/ message call Cooperative Office _ v1.8.1.zip-d / var/www/.. .. / / unpack and deploy in place

2) add virtual hosts for oa.tedu.cn

[root@svr7 ~] # vim / etc/httpd/conf.d/vhosts.conf / / configure virtual host ServerName oa.tedu.cn DocumentRoot / var/www/oa [root@svr7 ~] # systemctl restart httpd/ / restart Web service

Step 3: prepare the dedicated database oadb and authorize the user runoa to have full access

If you have done this before, you can skip it here.

[root@svr7 ~] # mysql-uroot-ppwd@123 / / Connect MariaDB [(none)] > CREATE DATABASE oadb; / / build Library MariaDB [(none)] > GRANT all ON oadb.* to runoa@localhost IDENTIFIED BY 'pwd@123'; / / authorized user MariaDB [(none)] > QUIT / / exit [root@svr7 ~] #

Step 5: adjust the attribution of the / var/www/oa directory so that the Web service has write permission

[root@svr7] # chown-R apache / var/www/oa/ [root@svr7] # ls-ld / var/www/oa/drwxr-xr-x. 10 apache root 231 September 24 22:27 / var/www/oa/

Step 5: visit http://oa.tedu.cn/ and follow the prompts to install the OA system

Launch Firefox Firefox on the svr7 host, visit http://oa.tedu.cn/, and you can see the installation page of the signaling collaboration system, as shown in figure-8.

Click "got it", fill in the database connection information correctly on the next page, as shown in figure-9, and then click "submit installation".

Note: if prompted "cannot write to folder Webmain", check that the SELinux security mechanism is turned off.

After completing the installation, follow the page prompts to delete the installation directory, record the default administrative user (admin) and password (123456), as shown in figure-10, and then click "go to login page".

If you log in successfully, you can see the management interface of the information system, as shown in figure-11.

6 case 6: enterprise OA system migration

6.1 question

This example requires you to be more familiar with the backup and recovery operations of the website and database through the offline migration process of the LAMP website platform, and complete the following tasks:

1) back up the OA system website and database on svr7.tedu.cn

2) prepare a new virtual machine (svr8.tedu.cn-- > 192.168.10.8)

Install and start the LAMP website platform to register the local domain name oa.tedu.cn-- > 192.168.10.8

3) migrate the OA system to svr8.tedu.cn through backup

4) access http://oa.tedu.cn/ on svr8 to verify the result

6.2 steps

To implement this case, you need to follow these steps.

Step 1: back up the OA system website and database on svr7.tedu.cn

1) back up the website

Stop the Web service:

[root@svr7 ~] # systemctl stop httpd

Perform a backup:

[root@svr7 ~] # tar-zcPf / root/oa_web.tgz / etc/httpd/conf.d/vhosts.conf / var/www/oa/ Note option P is uppercase

2) back up the database

Perform a backup:

[root@svr7 ~] # mysqldump-uroot-ppwd@123-- databases oadb > / root/oa_database.sql / / backup in multi-library mode

Confirm the backup file:

[root@svr7] # ls-lh / root/oa_*-rw-r--r--. 1 root root 1021K September 25 02:12 / root/oa_database.sql-rw-r--r--. 1 root root 2.0m September 25 02:06 / root/oa_web.tgz

Step 2: prepare a new virtual machine (svr8.tedu.cn  192.168.10.8)

1) install and launch the LAMP website platform

[root@svr8 ~] # yum-y install httpd mariadb-server maria php php-mysql [root@svr8 ~] # systemctl restart httpd mariadb / / start the website and database service [root@svr8 ~] # systemctl enable httpd mariadb

2) register the local domain name oa.tedu.cn, corresponding to the IP address 192.168.10.8

[root@svr8 ~] # vim / etc/hosts192.168.10.8 oa.tedu.cn

Step 3: migrate the OA system to svr8.tedu.cn through backup

1) upload backup data

Be careful to upload the backup files of websites and database materials on svr7 to svr8 in advance. For example, you can use scp to upload backup files directly on svr7.

[root@svr7] # scp / root/oa_* root@192.168.10.8:/root root@192.168.10.8's password: oa_database.sql 100% 518KB 51.1MB/s 00:00 oa_web.tgz 100% 1947KB 65.4MB/s 00:00 [root@svr7 ~] #

2) confirm the backup data on svr8

[root@svr8 ~] # ls-lh / root/oa_*-rw-r--r--. 1 root root 518K January 15 18:11 / root/oa_database.sql-rw-r--r--. 1 root root 2.0m January 15 18:11 / root/oa_web.tgz

3) restore websites and databases

[root@svr8 ~] # tar-xPf / root/oa_web.tgz / / Import website document [root@svr8 ~] # mysql-uroot

< /root/oa_database.sql //导入数据库 4）准备数据库用户 [root@svr8 ~]# mysql -uroot //新数据库服务器无密码MariaDB [(none)]>

GRANT all ON oadb.* to runoa@localhost IDENTIFIED BY 'pwd@123'; / / authorized user MariaDB [(none)] > QUIT; / / exit [root@svr8 ~] #

Step 4: access http://oa.tedu.cn/ on svr8 to verify the result

After the migration is complete, you can access http://oa.tedu.cn/ on svr8 and directly obtain the OA platform that was originally running on svr7. You can log in directly through the administrator admin, and the data is intact, as shown in figure-12.

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.