Documentation by Black Sheep Research

User Tools

Site Tools

Trace:
useful:mysql-mariadb

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
useful:mysql-mariadb [2022/11/27 15:40] – adminuseful:mysql-mariadb [2024/12/02 16:49] (current) – admin
Line 2: Line 2:
  
 ====== MySQL / MariaDB ====== ====== 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 ===== ===== Resync replication =====
Line 48: Line 57:
 Edit /etc/systemd/system/mariadb.service and change ProtectHome to be false (ProtectHome=false). Save the file, then: Edit /etc/systemd/system/mariadb.service and change ProtectHome to be false (ProtectHome=false). Save the file, then:
  
-    sudo systemd daemon-reload # refresh the systemd services so it sees your override file+    sudo systemctl daemon-reload # refresh the systemd services so it sees your override file
     sudo systemctl start mysql # and it should finally start     sudo systemctl start mysql # and it should finally start
  
Line 60: Line 69:
  
 The solution I have now used is well described at [[https://gist.github.com/drmalex07/c0f9304deea566842490|this Github article]]. 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 ===== ===== Data files under /home directory =====
Line 77: Line 89:
            
 but when I tried, it seemed not to work. 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;
 +
useful/mysql-mariadb.1669563610.txt.gz · Last modified: 2022/11/27 15:40 by admin