Differences

This shows you the differences between two versions of the page.

--- useful:mysql-mariadb [2022/08/11 17:26] – created admin
+++ useful:mysql-mariadb [2024/12/02 16:49] (current) – admin
@@ Line 2: / Line 2: @@
 ====== 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 =====
@@ Line 10: / Line 23: @@
      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;