felipenoris/mysql_normal_user.md

## mysql_normal_user.md

      
    Raw
  

              mysql_normal_user.md
            
          
    How to create and run a MySQL instance as a normal user (no root)

On Centos7, install libaio dependency:
# yum -y install libaio
Download precompiled Compressed TAR Archive Mysql Community Server for generic linux.


Website


Download Link for 64-bit v8.0.12


$ cd

$ mkdir local

$ cd local

$ wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz

$ tar xf mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz

$ rm mysql-8.0.12-linux-glibc2.12-x86_64.tar.xz

$ mkdir bin

$ ln -s ~/local/mysql-8.0.12-linux-glibc2.12-x86_64/bin/mysqld ~/local/bin/mysqld

$ ln -s ~/local/mysql-8.0.12-linux-glibc2.12-x86_64/bin/mysql ~/local/bin/mysql

$ export PATH=$HOME/local/bin:$PATH
Create a file ~/local/mysql.cnf with these contents.
Note that my username is felipenoris and my home dir is /home/felipenoris/, as an example.
[mysqld]
user=felipenoris
basedir=/home/felipenoris/mysql
datadir=/home/felipenoris/mysql/data
general-log-file=/home/felipenoris/mysql/general_log_file.log
pid-file=/home/felipenoris/mysql/pid_file.pid
slow-query-log-file=/home/felipenoris/mysql/slow_query_log.log
socket=/home/felipenoris/mysql/mysql.sock

Create mysql and data directories in the paths listed in mysql.cnf.
mkdir /home/felipenoris/mysql
mkdir /home/felipenoris/mysql/data
Initialize the database. Here I'm using --initialize-insecure option to create a root user with a blank password.
$ mysqld --defaults-file=/home/felipenoris/local/mysql.cnf --initialize-insecure
The output should be:
2018-09-29T17:22:38.617519Z 0 [System] [MY-013169] [Server] /home/felipenoris/local/mysql-8.0.12-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.12) initializing of server in progress as process 674
2018-09-29T17:22:38.617577Z 0 [ERROR] [MY-010338] [Server] Can't find error-message file '/home/felipenoris/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
mbind: Operation not permitted
mbind: Operation not permitted
mbind: Operation not permitted
mbind: Operation not permitted
2018-09-29T17:22:48.331010Z 5 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2018-09-29T17:22:57.915157Z 0 [System] [MY-013170] [Server] /home/felipenoris/local/mysql-8.0.12-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.12) initializing of server has completed

Then, start the server with:
$ mysqld --defaults-file=/home/felipenoris/local/mysql.cnf
The output should be:
2018-09-29T17:25:51.921788Z 0 [System] [MY-010116] [Server] /home/felipenoris/local/mysql-8.0.12-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.12) starting as process 773
2018-09-29T17:25:51.921837Z 0 [ERROR] [MY-010338] [Server] Can't find error-message file '/home/felipenoris/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
mbind: Operation not permitted
mbind: Operation not permitted
mbind: Operation not permitted
mbind: Operation not permitted
2018-09-29T17:25:52.783725Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2018-09-29T17:25:52.830378Z 0 [System] [MY-010931] [Server] /home/felipenoris/local/mysql-8.0.12-linux-glibc2.12-x86_64/bin/mysqld: ready for connections. Version: '8.0.12'  socket: '/home/felipenoris/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

This shell session will be blocked executing the mysql daemon. Note that the server has started with socket file /home/felipenoris/mysql/mysql.sock and will be listening for connections on port 3306.
To connect to the mysql instance, open a new terminal and use:
$ mysql --defaults-file=/home/felipenoris/local/mysql.cnf --socket=/home/felipenoris/mysql/mysql.sock -u root
This will open a mysql shell
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.12 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

Set the root password with command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';

For new connections, use -p option.
$ mysql --defaults-file=/home/felipenoris/local/mysql.cnf --socket=/home/felipenoris/mysql/mysql.sock -u root -p
At this point, the server is up and running. Read through getting started section of the MySQL manual to configure your database.
Connecting from Julia

using MySQL, DataFrames
c = MySQL.connect("localhost", "root", "MyNewPass", db="mysql", unix_socket="/home/felipenoris/mysql/mysql.sock")
df = MySQL.query(c, "SELECT * FROM payment", DataFrame)

MySQL.execute!(c, "BEGIN")
MySQL.execute!(c, "DELETE FROM payment")
df = MySQL.query(c, "SELECT * FROM payment", DataFrame)
MySQL.execute!(c, "ROLLBACK")
df = MySQL.query(c, "SELECT * FROM payment", DataFrame)