Welcome to the Tiny Core Linux Wiki at tinycorelinux.net!

You are here: welcome » wiki » mysql_persistence_guide

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
wiki:mysql_persistence_guide [2015/04/06 05:54] – [What is MariaDB] bmarkuswiki:mysql_persistence_guide [2022/09/21 17:10] (current) – external edit 127.0.0.1
Line 1: Line 1:
 +ARTICLE IS UNDER CONSTRUCTION !!!
 +
 +====== What is MariaDB ======
 +
 +MariaDB is an enhanced, drop-in replacement for MySQL.
 +
 +====== Installing MariaDB ======
 +
 +First of all you will need to have the MariaDB server and client extensions installed -- you can do this through the CLI
 +
 +  tce-load -wi mariadb.tcz mariadb-client.tcz
 +
 +Or through the GUI app browser in tiny core. 
 +
 +Your MariaDB installation will auto generate two items of interest '/etc/my.cnf' and '/usr/local/mysql/data/' 
 +
 +You will want to copy both these items to a persistent  area at a later stage, you have a choice of either adding these items to your filetool.lst, or to your persistent storage medium i.e. HDD or USB
 +
 +I will talk you through both. 
 +
 +====== Start and stop MySQL ======
 +
 +MySQL daemon must be running to work with databases. Use Services menu of Control Panel or following command:
 +
 +  /etc/init.d/services/mysql start
 +
 +To start it automatically at boot time, add command to /opt/bootlocal.sh
 +  
 +To stop daemon:  
 +
 +  /etc/init.d/services/mysql stop
 +  
 +To stop it automatically add command to /opt/shutdown.sh
 +  
 +
 +====== MariaDB Settings ======
 +=== Root password ===
 +
 +
 +To set up a root password you need to use the following command;
 +
 +  mysqladmin -u root password YOUR_PASSWORD
 +
 +=== Entering the MySQL terminal ===
 +  
 +To create a user you will first need to login to the MySQL terminal using the following command;
 +
 +  mysql -u root -p
 +  
 +Input your password when asked and you will be presented with the MySQL prompt.
 +
 +  mysql> 
 +
 +=== Creating a User ===
 + 
 +To actually create a user, use this command -- in this example, the user is 'nickjf20', password 'YOUR_PASSWORD'; 
 +
 +  CREATE USER 'nickjf20'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';
 +
 +=== Creating a Database ===
 +  
 +To create a database, use this command -- in this example, the database name is 'forums' 
 +
 +  CREATE DATABASE forums;
 +  
 +=== Granting Privileges ===
 +
 +To grant the user 'nickjf20' every privilege on the 'forums' database use;
 +
 +  GRANT ALL PRIVILEGES ON forums.* TO 'nickjf20'@'localhost';
 +  
 +
 +
 +====== Persistance ======
 +
 +Now you have your initial MySQL settings sorted out, we will go onto making these changes permanent. 
 +
 +You have two options;
 +
 +  - Add items to filetool.lst (will require filetool -b every reboot)
 +  - Add items to persistent storage mediums i.e. HDD or USB
 +
 +I personally recommend adding the items to a persistent medium simply because it is safer and easier. 
 +If you server loses power for whatever reason, your data will revert to the last time filetool.sh -b was run with the filetool.lst method.
 +
 +The disadvantages are that the DB's aren't stored in RAM through the second method, meaning higher read/write times and added wear to your HDD / USB.
 +
 +
 +===== Filetool method =====
 +
 +With this method you must add the following directories and files to your /opt/.filetool.lst 
 +
 +  /usr/local/mysql/data
 +  
 +This will restore your MySQL settings and DB's on boot. 
 +
 +With the filetool method **you must** make sure that before you shutdown, any open connections to the MySQL DBs are closed, otherwise data loss will occur. You can safely shut down the MySQL daemon using the following command;
 +
 +  /etc/init.d/services/mysql stop
 +  
 +or using services control panel.
 +
 +
 +===== Persistent Storage Method =====
 +
 +In this method, you have a much lower chance of data loss, as it does not rely on regular 'doses' of 'sudo filetool.sh -b' to backup your DB's.
 +
 +First you will want to copy over your my.cnf and data directory over to a storage directory. In this case, I will be using the generic hdd -- it could be sda1, sda3, e.t.c. 
 +
 +  cp -fr /usr/local/mysql/data /mnt/hdd/database/data
 +  cp -f /etc/my.cnf /mnt/hdd/database/my.cnf
 +  
 +You will then need to add the following commands to /opt/bootlocal.sh, again substituting hdd with your storage device.
 +
 +  rm -fr /usr/local/mysql/data
 +  rm -f /etc/my.cnf
 +  ln -s /mnt/hdd/database/data /usr/local/mysql/data
 +  ln -s /mnt/hdd/database/my.cnf /etc/my.cnf
 +  /etc/init.d/services/mysql start
 +  
 +As long as no open, active connections are present on shutdown or crash, no data corruption or loss //should// occur. 
 +
 +
 +   
  
Print/export
QR Code
QR Code wiki:mysql_persistence_guide (generated for current page)