Skip to content

Instantly share code, notes, and snippets.

@smohadjer

smohadjer/MariaDB.md

Last active Apr 22, 2020
Embed
What would you like to do?
Tips on using MariaDB

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 mysql@5.7
brew link --force mysql@5.7
brew services stop|start mysql@5.7

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     |
+--------------+-------------+----------------------------+------------------------+----------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.