Posts Categorized: mysql

Resetting MySQL root password on newer MySQL 5.7

In newer versions of MySQL it looks like the ‘Password’ field has been replaced with the ‘authentication_string’ field so older instructions online may not work. Stop the currently running MySQL service: systemctl stop mysql Start mysql skipping permissions tables: mysqld_safe –skip-grant-tables Then in another terminal run the mysql client: mysql Then the following SQL: UPDATE… Read more »

MySQL Percona Slave Replication Change Channel Name

I had inadvertently set up slave replication using the wrong channel name – which is a minor issue except that it caused some issues with an already configured Nagios check. To ‘change’ the channel name I stopped the slave process, removed all the slave definitions and used ‘CHANGE MASTER’ to re-create the slave from the… Read more »

Password-less Login to the MySQL Command Line

The goal here was to be able to log in to the MySQL command prompt be simply typing mysql rather than: mysql -u root -p This is particularly useful for automation of routine backups etc. mysql_config_editor The command mysql_config_editor has several options (e.g. add, remove, print) for managing encrypted passwords. To create a new login… Read more »

Connecting in to Docker MySQL Instance

Just recording a few of these steps for future reference. Pull the MySQL image from the hub: docker pull mysql/mysql-server:latest Run an instance: docker run -it -d -p 33060:3306 –name mysql-for-jonny -e MYSQL_ROOT_PASSWORD=opensesame -d mysql/mysql-server:latest Run a shell on the instance: docker exec -it mysql-for-jonny /bin/bash Once inside the instance use the MySQL client: mysql… Read more »

MySQL GRANT SELECT on a demo database to all users

MySQL access to all users

I needed to give all students read access to a demo database and did so with: mysql> GRANT SELECT ON gameofthrones.* TO ”@localhost; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec)    

MySQL dummy placeholder RPM package for MariaDB

On CentOS 7 MariaDB has (quite rightfully IMHO) replaced MySQL in the repositories however this has caused me a few problems. Since MySQL was so widely used there are other things that have MySQL as a requirement/dependency. Mainly my problem was some puppet modules that wanted the mysql-server package installed before they would proceed with… Read more »

MySQL: Give a User Grant Permissions

I needed to create a new MySQL user that would be able to create new users itself and grant permissions to the accounts that it creates. To do so I used the following: GRANT ALL PRIVILEGES ON *.* TO ‘subadmin’@’%’ WITH GRANT OPTION;    

Beginning PostgreSQL

As a MySQL user for a number of years I needed a translation tutorial from MySQL to PostgreSQL. So to get started on CentOS: Installation yum install postgresql-server If you also want the phpPgAdmin WUI: yum install phpPgAdmin php-pgsql httpd Create the configuration file and initialise the database: cp /usr/share/pgsql/postgresql.conf.sample /var/lib/pgsql/data/postgresql.conf /etc/init.d/postgresql initdb Make changes… Read more »

LAMP Performance

The following scripts can help in determining good values for Apache and MySQL configurations based on the amount of memory available: MySQL Tuner Apache Tuner Apache Buddy The following will giveĀ  a list of Apache Modules loaded: apachectl -t -D DUMP_MODULES The following will give the memory usage of Apache: #!/bin/sh # apache_mem.sh # Calculate… Read more »

LAMP on Ubuntu

There are several different commands that can be used to install Apache and MySQL services with PHP support: sudo apt-get install tasksel && sudo tasksel sudo apt-get install lamp-server^ sudo apt-get install apache2 libapache2-mod-php5 php5 php5-cli php5-mysql php5-gd php5-mcrypt php5-curl mysql-client mysql-server libmysqlclient15-dev phpmyadmin   For number two the carat at the end is important.