MariaDB

MariaDB.md

Installing MariaDB Server on macOS Using Homebrew

https://mariadb.com/kb/en/installing-mariadb-on-macos-using-homebrew/

Find out if MariaDB is installed

mariadb --version

---

If after a Mac OS update MariaDB or MySQL stop working and you get this error on login:\

mysql: Can't read dir of '/usr/local/etc/my.cnf.d' (Errcode: 2 "No such file or directory")
Fatal error in defaults handling. Program aborted

It's because my.conf.d folder doesn't exist. This will fix it:
mkdir /usr/local/etc/my.cnf.d

---

my.cnf  /usr/local/etc
Sequel Pro: https://www.sequelpro.com/

To find processes running and killing them
ps -ax | grep mysql
kill processPID

brew install mariadb 
brew install [email protected]
brew services stop|start|restart mariadb
brew services start mariadb --skip-grant-tables
brew services start [email protected]
brew services list

To login:
mysql -u root -p
sudo mysql -u root

If you get error with mysql -u root, do this after loging in with sudo mysql -u root:
GRANT ALL PRIVILEGES on *.* to 'root'@'localhost' IDENTIFIED BY '<password>';
FLUSH PRIVILEGES;
(replacing <password> with your desired mysql root password). This enables password logins for the root user.


CREATE DATABASE databaseName;
Use databaseName;  

Create one table with three fields
CREATE TABLE user_review (
  id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  reviewer_name CHAR(100),
  star_rating TINYINT,
  details VARCHAR(4000)
  ); 

Add two examples to table
INSERT INTO user_review (reviewer_name, star_rating, details) VALUES ('Ben', '5', 'Love the calzone!');
INSERT INTO user_review (reviewer_name, star_rating, details) VALUES ('Leslie', '1', 'Calzones are the worst.');

Useful commands:

SHOW DATABASES;
SHOW TABLES;
exit;  // to disconnect from database
select version(); // to find out version of mariadb installed
SELECT User FROM mysql.user;
select user, password, host from mysql.user; // to see list of users
MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;
MariaDB [(none)]> SELECT @@global.time_zone, @@session.time_zone, @@system_time_zone;
SELECT @@time_zone;

To set password for a user:

SET PASSWORD FOR 'bob'@'localhost' = PASSWORD('newpass');

Create a user for database:

GRANT ALL ON database_name.* TO 'database_user'@'localhost' IDENTIFIED BY 'user_password';

or:

CREATE USER 'user1'@localhost IDENTIFIED BY 'password1';

---

Changing character set of database to latin1

1. Login to your mariadb database
2. Run following command:

alter database myDatabaseName collate = 'latin1_swedish_ci';

To verify the change run:

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'myDatabaseName';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | ril         | latin1                     | latin1_swedish_ci      | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+

---

Setting Time Zone of MariaDB to UTC

1. If you are logged in then exit mariadb and stop it via: brew services stop mariadb
2. To populate time zone tables: To load the time zone tables from the command line, pass the zoneinfo directory path name to mysql_tzinfo_to_sql and send the output into the mysql program. If your root user has no password drop -p from below command:

   mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
   

3. Open my.cnf (/usr/local/etc) and add following lines to it:

   [mysqld]
   default_time_zone='UTC'
   

4. Restart Database: brew services restart mariadb
5. Login to database and check whether time zone tables are populated or not. If they are not the count below will be 0:

   SELECT COUNT(*) FROM mysql.time_zone_name;
   +----------+
   | COUNT(*) |
   +----------+
   |      594 |
   +----------+
   

6. Check the time zone:

   MariaDB [(none)]> SELECT @@time_zone;
   +-------------+
   | @@time_zone |
   +-------------+
   | UTC         |
   +-------------+