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.