Documentation by Black Sheep Research

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
[[useful:contents|Contents Page]]

====== MySQL / MariaDB ======

===== Set up replication =====

This is the command to set everything:

     CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000673', MASTER_LOG_POS=4, MASTER_USER='replicator', 
     MASTER_PASSWORD='*******', MASTER_HOST='127.0.0.1', MASTER_PORT=4002;
     
where the numbers 000673 and 4 will need to be changed.

===== Resync replication =====

This deserves [[useful:mysql-replication|a page to itself]].

===== Log all queries =====

To change in the current running server:

     SET GLOBAL log_output = 'FILE';
     SET GLOBAL general_log_file = '/var/log/mysql/general.log';
     SET GLOBAL general_log = 'ON';
     
===== Try to move past replication error =====

This assumes that replication was previously working. 

It is easy for replication to be upset slightly, resulting in an error on the slave that stops it operating. It then gets behind the master and manual intervention is needed.

Sometimes more substantial intervention is needed, but often bypassing the error is all that is needed. Before you start, check the situation by going into a MySQL client and entering:

    show slave status\G

Look at the error to see whether it is the sort of thing that can reasonably be ignored on a slave. If so, in a MySQL client enter the following statements:

    stop slave;
    set global sql_slave_skip_counter = 1;
    start slave;
    show slave status\G

Or, combine the first three on a single line:

    stop slave; set global sql_slave_skip_counter = 1; start slave;
    
It's best to pause before checking slave status, as failures may soon reoccur.  

Check whether the slave is now running. Sometimes this has to be repeated – checking each time that the error can be ignored. Often this will get replication working again and the slave will catch up with the master.

===== MySQL blocks access to data in /home directories =====

This may be blocked by the MariaDB service. To fix correctly override the service file with a local copy:

    sudo cp /lib/systemd/system/mariadb.service /etc/systemd/system

Edit /etc/systemd/system/mariadb.service and change ProtectHome to be false (ProtectHome=false). Save the file, then:

    sudo systemctl daemon-reload # refresh the systemd services so it sees your override file
    sudo systemctl start mysql # and it should finally start

===== SSH Tunnel to Database =====

If the MySQL or MariaDB database is running on a different machine (whether real or virtual) from the application, a connection through a network is needed. Assuming the network cannot be assumed to be secure, some kind of encryption is needed.

Various alternatives exist, the main ones being and SSH tunnel, TLS connection, VPN. Of these, the SSH tunnel is the easiest to configure, and also the most efficient.

I used to set up SSH tunnels with autossh which provides a means of restarting SSH if it should fail. More recently, I found that it is possible in systems that support systemd to run the tunnel as a service. The service mechanism will handle the restarting without the need for extra software. The creation of tunnels can also be made quite elegant.

The solution I have now used is well described at [[https://gist.github.com/drmalex07/c0f9304deea566842490|this Github article]].

Except that I had a big problem with one connection, and it seems the fix is to change "LOCAL_ADDR=localhost" to "LOCAL_ADDR=127.0.0.1".


===== Data files under /home directory =====

By default, this is not allowed and will cause errors. But there are situations where /home is a good place for the data. To fix this in Debian, create and edit this file:

     vim /etc/systemd/system/mariadb.service.d/override.conf
     
Set the contents to:

     [Service]
     ProtectHome=false

It should be possible to achieve this without knowing exactly where the file is by using:

     systemctl edit mariadb
     
but when I tried, it seemed not to work.

===== Change collation for all tables in a database =====

Remember to set the correct name for the database in this code and make sure output file does not exist:

     SET @MY_SCHEMA = "myremos_j41";
     SELECT DISTINCT
      CONCAT("ALTER TABLE ", TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;") as queries
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA=@MY_SCHEMA
       AND TABLE_TYPE="BASE TABLE"
     INTO OUTFILE '/tmp/convert.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
     
Then run this command (changing database name as appropriate):

     mysql myremos_j41 < /tmp/convert.csv     
     
===== Get basename or dirname from a file path or similar =====

     SUBSTRING_INDEX(file_path, '/', -1) AS basename
     
     LEFT(file_path, LENGTH(file_path) - LENGTH(SUBSTRING_INDEX(file_path, '/', -1)) - 1) AS dirname
     
===== Create a new root user =====

     CREATE USER 'username'@'localhost' IDENTIFIED BY 'the_password';
     GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;