Két fontos rendszerfelhasználó

  • Beküldve: 2016. január 07, 14:53
  • Szerző: Bolemányi Attila

Az első a debian-sys-maint felhasználó, akinek a segítségével az adatbázisok karbantartási feladatai futnak le. A felhasználónév és a hozzá tartozó jelszó az egyes debian.cnf fájl példányokban található meg, a kapcsolódáshoz szükséges egyéb információkkal (pl. socket) együtt. Készítsük el a /etc/mysql/restore.sql fájlt ezzel a tartalommal:

GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'uWDfNoDMMISVV13l' WITH GRANT OPTION;
FLUSH PRIVILEGES;

A fájlban található jelszót - IDENTIFIED BY 'uWDfNoDMMISVV13l' - a saját debian.cnf fájl példányodból kell kimásolnod! A debian-sys-maint felhasználót a 3307, 3308 és 3309-es példányhoz kell hozzáadnod, mivel a 3306-os példányhoz a MariaDB csomag telepítésekor már hozzáadásra került:

mysql -h 127.0.0.1 --port=3307 -u root -D mysql < restore.sql
mysql -h 127.0.0.1 --port=3308 -u root -D mysql < restore.sql
mysql -h 127.0.0.1 --port=3309 -u root -D mysql < restore.sql

A másik felhasználó a multi_admin, akire az adatbázis-kiszolgáló példányok szabályos leállításához lesz szükségünk. Készítsük el a /etc/mysql/multi_admin.sql fájlt az alábbi tartalommal:

GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'Pa$$w0rd';
flush privileges;

Adjuk hozzá a felhasználót mind a négy kiszolgálóhoz:

mysql -h 127.0.0.1 --port=3306 -u root -p < multi_admin.sql
mysql -h 127.0.0.1 --port=3307 -u root < multi_admin.sql
mysql -h 127.0.0.1 --port=3308 -u root < multi_admin.sql
mysql -h 127.0.0.1 --port=3309 -u root < multi_admin.sql

Helyezzük biztonságba az új MariaDB példányokat is:

mysql_secure_installation -h 127.0.0.1 -P 3307
mysql_secure_installation -h 127.0.0.1 -P 3308
mysql_secure_installation -h 127.0.0.1 -P 3309

A folyamat menete az alábbi:

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] y
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] y
 ... 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] y
 ... 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] y
 - Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - 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] y
 ... 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!

Fontos: a fenti folyamat során adjuk meg az új példányok root felhasználójának jelszavát is (most: Pa$$w0rd).

Futtassuk le a /etc/mysql/debain-startxxxx szkripteket is:

root@jessie:/etc/mysql# ./debian-start
Checking for corrupt, not cleanly closed and upgrade needing tables.

Az eredmény a syslogban:

Jan  7 10:53:14 jessie ./debian-start[8806]: Upgrading MySQL tables if necessary.
Jan  7 10:53:14 jessie ./debian-start[8809]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jan  7 10:53:14 jessie ./debian-start[8809]: Looking for 'mysql' as: /usr/bin/mysql
Jan  7 10:53:14 jessie ./debian-start[8809]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jan  7 10:53:14 jessie ./debian-start[8809]: This installation of MySQL is already upgraded to 10.1.10-MariaDB, use --force if you still need to run mysql_upgrade
Jan  7 10:53:14 jessie ./debian-start[8816]: Checking for insecure root accounts.
Jan  7 10:53:14 jessie ./debian-start[8820]: Triggering myisam-recover for all MyISAM tables

Tehát az első adatbázis-kiszolgálónk hiba nélkül működik.

root@jessie:/etc/mysql# ./debian-start3307
Checking for corrupt, not cleanly closed and upgrade needing tables.

Az eredmény a syslogban:

Jan  7 10:53:51 jessie ./debian-start3307[8857]: Upgrading MySQL tables if necessary.
Jan  7 10:53:52 jessie ./debian-start3307[8860]: /usr/bin/mysql_upgrade: the '--basedir' option is always ignored
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Looking for 'mysql' as: /usr/bin/mysql
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Phase 1/6: Checking and upgrading mysql database
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Processing databases
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.column_stats                                 OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.columns_priv                                 OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.db                                           OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.event                                        OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.func                                         OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.gtid_slave_pos                               OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.help_category                                OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.help_keyword                                 OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.help_relation                                OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.help_topic                                   OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.host                                         OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.index_stats                                  OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.innodb_index_stats                           OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.innodb_table_stats                           OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.plugin                                       OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.proc                                         OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.procs_priv                                   OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.proxies_priv                                 OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.roles_mapping                                OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.servers                                      OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.table_stats                                  OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.tables_priv                                  OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.time_zone                                    OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.time_zone_leap_second                        OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.time_zone_name                               OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.time_zone_transition                         OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.time_zone_transition_type                    OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql.user                                         OK
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Phase 2/6: Fixing views
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Processing databases
Jan  7 10:53:52 jessie ./debian-start3307[8860]: information_schema
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql
Jan  7 10:53:52 jessie ./debian-start3307[8860]: performance_schema
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Phase 3/6: Running 'mysql_fix_privilege_tables'
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Phase 4/6: Fixing table and database names
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Processing databases
Jan  7 10:53:52 jessie ./debian-start3307[8860]: information_schema
Jan  7 10:53:52 jessie ./debian-start3307[8860]: mysql
Jan  7 10:53:52 jessie ./debian-start3307[8860]: performance_schema
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Phase 5/6: Checking and upgrading tables
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Processing databases
Jan  7 10:53:52 jessie ./debian-start3307[8860]: information_schema
Jan  7 10:53:52 jessie ./debian-start3307[8860]: performance_schema
Jan  7 10:53:52 jessie ./debian-start3307[8860]: Phase 6/6: Running 'FLUSH PRIVILEGES'
Jan  7 10:53:52 jessie ./debian-start3307[8860]: OK
Jan  7 10:53:52 jessie ./debian-start3307[8887]: Checking for insecure root accounts.
Jan  7 10:53:52 jessie ./debian-start3307[8891]: Triggering myisam-recover for all MyISAM tables

Tehát itt még volt némi teendő - konkrétan egy adatbázis-upgrade művelet - amely, hála a jól hozzáadott debian-sys-maint felhasználónak, hiba nélkül le is futott. A debian-start3308 és a debian-start3309 lefuttatásakor ugyanilyen eredményeket kell tapasztalnunk. Mindenesetre a syslogban ellenőrizd le. Ezzel gyakorlatilag készen vagyunk, a négy kiszolgáló vidáman működik.