mysql

Multiple MySQL Servers

How to run multiple instances of MySQL on the same computerr. Can be useful for advanced replication set-ups.

Official Documentation
http://linuxadminzone.com/setting-up-mutiple-mysql-database-servers-on-a-single-linux-machine/

Technology:

BigDump

BigDump allows you to import large MySQL databases that are too large for phpMyAdmin.

Technology:

Performance Tuning

Various tips and suggestions for optimizing MySQL performance.

MySQLTuner
http://blog.mysqltuner.com
Tuning MySQL Performance with MySQLTuner
Download the MySQLTuner script:

cd /extra/src
wget http://mysqltuner.pl/ -O mysqltuner.pl

Running it is very simple:

Technology:

Replication

How to synchronize MySQL databases between multiple servers. Also a good means of backing up a live server.

Configure the Master
Edit my.cnf and so that the MySQL server is listening on the network. You might have to comment out:

#skip-networking
#bind-address = 127.0.0.1

Also in my.cnf, specify for which database you want to write logs for, where that log file will be (make sure any directory exists and is writeable by the MySQL user) and specify that this MySQL server is the master.

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=exampledb

Technology:

Binary Logs

The MySQL binary log contains all statements that update data along with statements that potentially could have updated it. They are used to recover a database, and required if you want to replicate it. However, when the database changes frequently, they build up and can consume a lot of disk space.

Don't just delete the binary log files in /usr/local/mysql/var! Doing so really screws up MySQL!
Automatically purging binary logs
Edit /etc/my.cnf and add/edit a expire_logs_days variable. For example, to purge logs older than 30 days:

expire_logs_days=30

Purge existing binary logs
Look in /usr/local/mysql/var and to check the name of the oldest binary log file you want to keep

/usr/local/mysql/bin/mysql -u root -p
purge master logs to ‘mysql-bin.010′;

Technology:

Storage Engines

  • InnoDB
    Basic configuration of InnoDB in /etc/my.cnf:

Technology:

Working with Databases

Command-line commands for common tasks. (http://www.pantz.org/software/mysql/mysqlcommands.html)

List Databases

SHOW DATABASES;

Switch to a Database

USE DB_NAME;

List Tables in a Database

SHOW TABLES:

Create a database

/usr/local/mysql/bin/mysqladmin -u <username> -p create <databasename>

Create a user for a database
Login to the MySQL server:

/usr/local/mysql/bin/mysql -u <username> -p

Create the user and grant them privileges:

Technology:

phpMyAdmin

phpMyAdmin is a browser-based utility for managing MySQL databases.

Prerequisites
phpMyAdmin likes to have libmcrypt installed with your PHP:

yum -y install libmcrypt-devel

Then compile your PHP with mcrypt support by adding --with-mcrypt to it's configure options.

Webserver Configuration
Create a base directory for the your private, local site:

mkdir -p /var/websites/private/logs/
mkdir -p /var/websites/private/htdocs
chown nobody:nobody /var/websites/private/logs
chown nobody:nobody /var/websites/private/htdocs
  • Apache

Technology:

MySQLdb

Instruction for installing Trac using MySQL instead of SQLite

Install MySQL per the instructions here.However, add --enable-thread-safe-client to the configure options.
Edit your ~/.bash_profile and add your MySQL binaries:

PATH="${PATH}:/usr/local/mysql/bin"

Then, install MySQLdb. First, download a copy of the latest version:

cd /extra/src
wget http://superb-east.dl.sourceforge.net/sourceforge/mysql-python/MySQL-python-1.2.2.tar.gz
tar zxvf MySQL-python-1.2.2.tar.gz
cd MySQL-python-1.2.2

Technology:

Pages

Subscribe to RSS - mysql

Recent Updates

  • nginx
    8 months 3 weeks ago
    1.27.2 update
  • lighttpd
    9 months 3 days ago
    Drupal 10/11 config
  • Drupal 11
    9 months 5 days ago
  • PHP
    9 months 5 days ago
    PHP 8.3.11 and AlmaLinux
  • Pound SSL Proxy
    9 months 5 days ago
    New version of Pound