Posts tagged 'mysql'

Connecting to MySQL 8

published on March 24, 2018.

I’ve used recently PHPDocker.io to generate a set of Docker files for a pet project and it had the option to use MySQL 8 and of course I went with that. The problem was when I wanted to connect to the database that was on this MySQL 8 server.

I had locally installed the MySQL 5.7 client version and when trying to connect to the MySQL 8 server it complained about a missing authentication plugin:

ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded:
/usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

Turns out, in MySQL 8 this caching_sha2_password is the default authentication plugin instead of the mysql_native_password. This new authentication plugin is described in the documentation. I didn’t want to change anything in the MySQL docker image, so instead I decided to upgrade to MySQL 8 client on my Fedora. First I removed all traces of MySQL I had:

sudo dnf remove mysql

Then I installed the RPM from MySQL:

sudo dnf install https://dev.mysql.com/get/mysql57-community-release-fc27-10.noarch.rpm

And finally installed the MySQL 8 client:

sudo dnf --enablerepo=mysql80-community install mysql-community-client

And now I can connect to the MySQL 8 server inside the Docker container:

mysql -P 8082 -h 127.0.0.1 --protocol=tcp -utest -p test

Happy hackin’!

Tags: mysql, authentication.
Categories: Software, Development.

Backup script for mysql

published on November 05, 2010.
Heads-up! You're reading an old post and the information in it is quite probably outdated.

This post is more of a reminder for myself. Anywayz, a little bash script that backups a database, gzipit and deletes all backups older than 3 days.

#!/bin/bash

DBUSER="user"
DBPASS="pass"
DBDB="dbname"
NOW=$(date +"%Y-%m-%d-%H-%M-%S")
BACKUPROOTDIR="/tmp"
BACKUPSQL="$BACKUPROOTDIR/mysqlbackup-$NOW.sql"
BACKUPGZIP="$BACKUPSQL.gz"

mysqldump -u$DBUSER -p$DBPASS $DBDB > "$BACKUPSQL"
gzip -c $BACKUPSQL > $BACKUPGZIP
rm $BACKUPSQL
find $BACKUPROOTDIR -type f -name "mysqlbackup\*" -mtime +3 | xargs rm

Kudos to @zsteva for looking at it to spot any errors I might have made.

Tags: backup, mysql, script, shell.
Categories: Development.

Ubuntu as a dev machine

published on October 15, 2008.
Heads-up! You're reading an old post and the information in it is quite probably outdated.

This post is more of a note to myself, ‘cause I keep forgetting all these Linux commands, and spend hours setting up stuff right…

I’m installing Ubuntu 8.04 on VirtualBox, with windows xp as the host machine. I must do it this way, because my wireless card is having some problems with Linux, something with the drivers. The possible solution includes kernel compiling — thanks, but no thanks.

Anyway… The installation itself is no trouble, so I’ll skip that. I always keep the apt-cache from previous installations, sparing hours of updating the system… On the host I have a folder that I share between the host OS and the client OS and first I need to reach that folder, to get from it the apt-cache.

First, need to install the Guest Additions. In Virtualbox go to Devices —> Install Guest Additions. In the console run:

sudo /media/cdrom/VBoxLinuxAdditions.run

After it’s finished, we need to mount the shared folder:

sudo mount -t vboxsf name_of_the_sharing_folder /path/to/mount_point

Now, for me, this command shows some error. Here’s what I have to do:

sudo modprobe vboxfs
sudo mount -t vboxsf name_of_the_sharing_folder /path/to/mount_point

Something with some modules not being loaded into the kernel, not bothered with it really… Now I can copy the apt-cache to where it needs to be:

sudo cp -r /path/to/mount_point/apt-cache /var/cache/apt/archives

Now do the system update. If the system update includes a kernel update, you’ll have to install Guest Additions once more…

Next installing the LAMP:

sudo apt-get install apache2
sudo apt-get install php5 libapache2-mod-php5
sudo /etc/init.d/apache2 restart
sudo apt-get install mysql-server
sudo apt-get install libapache2-mod-auth-mysql php5-mysql phpmyadmin
sudo /etc/init.d/apache2 restart
sudo a2enmod rewrite
sudo /etc/init.d/apache2 restart

That should do it. But hey! mod_rewrite still doesn’t work!

sudo gvim /etc/apache2/sites-available/default

And change AllowOverride None to AllowOverride All.

There. I have a basic LAMP on Ubuntu under VirtualBox. I made a few snapshots of the VirtualBox image, in case I trash it (which probably will happen soon), so I don’t need to reinstall over again.

Now, I’m of to setup SVN…

Optimizing MySQL and a Zend_Db_Profiler example

published on October 13, 2008.
Heads-up! You're reading an old post and the information in it is quite probably outdated.

Last night I came across on a post on TechFounder, about using Zend_Db_Profiler and a good example with real data on optimizing MySQL queries. For “geeks” who SQL speak fluently this will probably be no new stuff, but for great number of web developers (me included!) this will probably come in handy.

Cheers!

Robert Basic

Robert Basic

Software engineer, consultant, open source contributor.

Let's work together!

If you require outsourcing or consulting help on your projects, I'm available!

Robert Basic © 2008 — 2018
Get the feed