Skip to content

Instantly share code, notes, and snippets.

@smohadjer

smohadjer/MariaDB.md

Last active Apr 28, 2021
Embed
What would you like to do?
MariaDB

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 mariadb@10.3
brew services stop|start|restart mariadb
brew services start mariadb --skip-grant-tables
brew services start mariadb@10.3
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.');

Create a user for database:
GRANT ALL ON reviews.* to review_site@localhost IDENTIFIED BY 'JxSLRkdutW';

Useful commands:
SHOW DATABASES;
SHOW TABLES;
exit;  // to disconnect from database
select version(); // to find out version of mariadb installed
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;

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         |
    +-------------+
    
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment