User:Elukey/Analytics/DBs

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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)