User:Elukey/Analytics/DBs

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';

if root@localhost is already created, just execute:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED VIA unix_socket WITH GRANT OPTION
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED VIA unix_socket WITH GRANT OPTION

Then check:

MariaDB [(none)]> select User,Host,plugin from mysql.user;
+------+-----------+-------------+
| User | Host      | plugin      |
+------+-----------+-------------+
| root | localhost | unix_socket |
| root | 127.0.0.1 | unix_socket |
| root | ::1       | unix_socket |
+------+-----------+-------------+
3 rows in set (0.00 sec)