User:Elukey/Analytics/DBs

From Wikitech
Jump to navigation Jump to search

Connect to the DBs

# From localhost
sudo mysql -h localhost --skip-ssl

Check DB table sizes

SELECT
     table_name AS `Table`,
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'staging'
ORDER BY (data_length + index_length) DESC LIMIT 50;

Stop everything on Analytics slaves

# Check running commands (like ALTERs)
sudo mysql -h localhost --skip-ssl <<< 'show full processlist \G';
# Stop MW slaves
sudo mysql -h localhost --skip-ssl <<< 'stop all slaves;'
# Stop eventlogging_sync
sudo service eventlogging_sync stop

First run of MariaDB

First of all, remember that MariaDB logs are in /var/lib/mysql (and not in log), very useful to debug what fails during startup (example: /var/lib/mysql/analytics-meta.err)

After the installation of the deb package on Stretch you need to make sure that /run/mysqld has been created, either manually or after a reboot (it is mounted to tmpfs at startup), otherwise Mariadb will not start. Then make sure to run the following (modify --basedir and --defaults-file accordingly):

# Install the Mysql database
sudo /opt/wmf-mariadb101/scripts/mysql_install_db --user=mysql --basedir=/opt/wmf-mariadb101 --datadir=/where/your/data/will/live

# Start Mariadb
sudo systemctl start mariadb

# Sets root password and remove cruft not needed among the default accounts
sudo /opt/wmf-mariadb101/bin/mysql_secure_installation --basedir=/opt/wmf-mariadb101 --defaults-file=/etc/my.cnf

Then if you are using the unix socket plugin, it would be wise to create a new specific root account and drop the others. To check what it is currently there, do the following query:

MariaDB [(none)]> select User,Host,plugin from mysql.user where user like 'root';

Create the new root user account and make sure to add the grants:

CREATE USER root@localhost IDENTIFIED VIA unix_socket;
MariaDB [(none)]> show grants;
+------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                      |
+------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                  |
+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Then you can drop the other root accounts and enjoy a password-less root login!

Otherwise, if root@localhost is already created, just execute:

MariaDB [(none)]> select User,Host,plugin from mysql.user where user like 'root';
+------+-----------+-----------------------+
| User | Host      | plugin                |
+------+-----------+-----------------------+
| root | localhost | mysql_native_password |
+------+-----------+-----------------------+
1 row in set (0.002 sec)

MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED VIA unix_socket;