MySQL on Localhost

Modern versions of MySQL is memory intensive; thus, it is a pre-requisite that an adequate amount of RAM is present on a MySQL server prior to its installation. However, slimmer instances of VPS or AWS/VMWare may not have sufficient memory to run MySQL. Hence, swap files must be created to augment the modest RAM allocation in these types of machines. Below is a procedure to add swap memory on a CentOS image:
 
# Check memory & swap file
free -m
 
# Check available disk space
df -h
 
# Allocate swapfile, set appropriate permissions, create swapfile
sudo dd if=/dev/zero of=/swapfile count=4096 bs=1MiB    #allocate
chmod 600 /swapfile        #secure the directory
mkswap /swapfile        #make swapfile in the /swapfile directory
swapon /swapfile        #configure system to use /swapfile
 
# Check swapfile settings
swapon -s
 
# Make permanent
vim /etc/fstab
# add this line
/swapfile none swap defaults 0 0
 

 
Run MySQL on Localhost:
 
# Reinstall MySQL
yum remove mysql mysql-server
mv /var/lib/mysql /var/lib/mysql_old_backup
# or just delete: rm -rf /var/lib/mysql
yum install mysql mysql-server
 
# Set default password format – this must be done prior to creating any new users!
vim /etc/my.cnf
# add this line
default_authentication_plugin=mysql_native_password
 
# Set mysql to autostart
systemctl enable mysqld
systemctl start mysqld
 
# Obtain the initial password
grep ‘temporary password’ /var/log/mysqld.log
 
# Secure mysql
mysql_secure_installation
# set password
# R3sponsibility2nd5ecurity!
 
# Send command from bash terminal
mysql -uroot -ppassword -e “COMMAND1;COMMAND2;”
 
# Example
mysql -uroot -ppassword -e “ALTER USER kimconnect IDENTIFIED BY ‘5ecret0fM@na\!’;”
 
# Create database, user & grant access
mysql -uroot -ppassword  -e “CREATE DATABASE kimconnect;CREATE USER kimconnect@’%’ IDENTIFIED BY ‘password’;GRANT ALL PRIVILEGES ON kimconnect.* TO ‘kimconnect’@’%’;flush privileges;”
 
 
# Individual commands:
 
# Set password
SET PASSWORD FOR ‘kimconnect’@’%’ = PASSWORD(‘password’);
 
# Update password
ALTER USER kimconnect IDENTIFIED BY ‘password’;
 
# Grant access
mysql -uroot -ppassword -e “GRANT USAGE ON kimconnect.* TO ‘kimconnect’@’%’ IDENTIFIED BY ‘password’;”
 
# Remove user
DROP USER kimconnect;
mysql -uroot -ppassword -e “DROP USER kimconnect;”
 
# Reload accesses
flush privileges;
 
# Grant remote accesses
mysql -uroot -ppassword-e “GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ WITH GRANT OPTION;FLUSH PRIVILEGES;”
mysql -uroot -ppassword -e “GRANT ALL PRIVILEGES ON kimconnect.* TO ‘kimconnect’@’%’ WITH GRANT OPTION;FLUSH PRIVILEGES;”
 
# Check connection
mysql -ukimconnect-ppassword -e “show databases;” -h 172.17.0.x
 

Troubleshooting
 
# connect to mysqld
mysql -uroot -p
# input password
# ERROR 2002 (HY000): ‘Can’t connect to local MySQL server through socket ‘/var/lib/mysqld/mysqld.sock’ (111)’
# This could be caused by an incorrect password. If necessary, restart mysqld
systemctl restart mysqld
 
# Check password policy
SHOW VARIABLES LIKE ‘validate_password%’;
# Change any global variable:
# SET GLOBAL validate_password_length = 12;
# SET GLOBAL validate_password_number_count = 3;
# SET GLOBAL validate_password_mixed_case_count = 1;
# SET GLOBAL validate_password_special_char_count = 1;
# SET GLOBAL validate_password_policy = HIGH;
 
# Look at all the sockets
find / -type s
# Located socket at: /run/mysqld/mysqlx.sock
pkill mysqld
 
Out of memory error:
InnoDB: Fatal error: cannot allocate memory for the buffer pool
# Resolution: increase RAM or add a swap file onto the host
 
ERROR 2059 (HY000): Plugin caching_sha2_password could not be loaded: Error loading shared library /usr/lib/mariadb/plugin/caching_sha2_password.so
# Resolution: 
vim /etc/my.cnf
# add this line
default_authentication_plugin=mysql_native_password

Leave a Reply

Your email address will not be published. Required fields are marked *