Mysql enable error logging

Was attempting to get the mysql error_log to work. The issue was that skip_log_error was being passed as mysqld param.

Using mariadb

# Is error_log set
$ cat /etc/mysql/mariadb.conf.d/50-server.cnf | grep log_error

[mysqld]
log_error = /var/log/mysql/error.log

# Is skip_log_error removed or commented out
$ cat /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf | grep skip_log_error

[mysqld_safe]
#skip_log_error

# You may already have mysqld running with skip-log-error param which will prevent error_log from working
$ ps aux | grep skip-log-error

mysql    29703  0.0  1.4 662768 168708 ?       Sl   May01   0:36 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --skip-log-error --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

http://x-v.it/09i2ls

# stop mysqld by killing process
$ kill 29703

# restart mysql
$ sudo /etc/init.d/mysql restart

# view log variables to determine if working
$ mysql -uUsername -pPassword -se "SHOW VARIABLES" | grep -e log_error -e general_log -e slow_query_log
mysql some log variables

Mysql Error: too many open files

# error: ERROR 1018 (HY000): Can’t read dir of ‘.’ (errno: 24)

# Super user
$ sudo su

# See what mysql files are open
$ lsof -u mysql

# Count how many mysql files are open
$ lsof -u mysql | wc -l

# close mysql files
$ kill -9 `lsof -t -u mysql`

# sources
http://i-learnings.blogspot.com.br/2012/11/error-1018-hy000-cant-read-dir-of-errno.html
http://www.webhostingtalk.com/showthread.php?t=1138305

Setting Master Slave Mysql Replication

Configuration and Setup

On Master and Slave

edit /etc/mysql/my.cnf
# http://stackoverflow.com/questions/8605318/mysql-replication-slave-server-on-one-database#answer-8606998
# http://forums.mysql.com/read.php?35,369191,370003
# if you only want to replicate a specified database you need to add line to my.cnf
# !!! IMPORTANT in selecting the correct database name or replication will run without errors but nothing will be updated

replicate-do-db=dbname

# name reported to the master during slave registration

report_host=hostname

# give a server id

server-id=1323

# give bin log file location

log_bin=/var/log/mysql/mysql-bin.log

master server configuration file changes

# restart database

$ sudo /etc/init.d/mysql restart

# add the user the slave will use

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepassword'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

On master

Dump mysql databases WITH compression

# all databases

$ mysqldump -uUSERNAME -pPASSWORD --all-databases --master-data | gzip > master_dbname_dump.sql.gz

# individual database

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data | gzip > master_dbname_dump.sql.gz
Dump mysql databases WITHOUT compression

# individual database

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data > master_dbname_dump.sql

# View Master information in the file dumped that will set the slave.

$ grep CHANGE *sql | head -1

# show slaves that are connected

mysql> SHOW SLAVE HOSTS;

# send file to slave server

$ scp master_dbname_dump.sql username@address.com:

On Slave

edit /etc/mysql/my.cnf
the slave server has a ‘read-only’ variable because that is its purpose. (Do not use this in master-master replication)
http://dba.stackexchange.com/questions/30128/should-a-mysql-replication-slave-be-set-to-read-only#answer-30129

read_only=ON

# The purpose of the import is because both the master and slave database must be the equal at point where the binlog started recording the replication.
# import the dump.

$ mysql -uroot < master_dbname_dump.sql

# Set the slave user

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';

# Set a user that will only have an read ability.
# This is a safeguard and repetitive because setting the 'read-only' variable in my.cnf would prevent slave writing. Slave should not have writing permissions.

mysql> CREATE USER 'read_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT SELECT ON *.* TO 'read_user'@'%';

# start slave

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G 

Notes

The location of either the master or slave logs are located in /var/log/mysql.
Its a binary(.bin) file contain the queries ran to sync the slave.

# view what inside the bin, mysql view queries logs

$ mysqlbinlog /var/log/mysql/mysql-bin.000001

# show the bin name

mysql> SHOW MASTER STATUS;
mysql> SHOW BINARY LOGS;

# dba.stackexchange.com/questions/47046/why-do-the-mysql-bin-log-files-still-exist-after-a-purge-or-flush#answer-47048
# to reset all the queries and purge all binary logs. This will also do the same in the slave.

mysql> RESET MASTER;

# Sometimes there's errors in the 'slave status' you can skip queries but more error may lie ahead. This is not a permanent fix.
# https://www.howtoforge.com/how-to-repair-mysql-replication

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

# the slave is working when you see this in the status

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

# To detect a Master and Slave being out of sync
# Pick any table and run CHECKSUM TABLE against a table on the Master and the Slave's copy of the same table.
# If the values do not come back the same, then something is out-of-sync.

mysql> CHECKSUM TABLE dbname.table;

Reset everything

On master when changing the replicate user password there was an error on the slave. Then the slave needed to be reset.

On Master

$ mysqldump -uUSERNAME -pPASSWORD --databases dbname --master-data | gzip > master_dbname_dump.sql.gz

# will clear/delete/purge all bin log. Be careful this may cause issues if there's mulitple slave to this master.

mysql> RESET MASTER;

On Slave

mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
mysql> RESET MASTER;  # will clear/delete/purge all bin log.

if this error 'ERROR 1186 (HY000): Binlog closed, cannot RESET MASTER' is because 'log_bin' was not specified in mysql configuration file my.cnf

# import the dump

$ mysql -uroot < master_dbname_dump.sql
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.3', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;

the slave is working when you see in the status

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Master to master replication

# simply make master a slave as well.
# https://www.digitalocean.com/community/tutorials/how-to-set-up-mysql-master-master-replication

mysql> CHANGE MASTER TO MASTER_HOST='hostname', MASTER_USER='repl', MASTER_PASSWORD='slavepassword';

# values need to be equal where the colors match
master-master relationship

Miscellaneous

# http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o#answer-3229580
# an option to reset the slave position

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;

Setting up Ubuntu Server on Laptop

Dell Latitude D630

Log MySQL requests/queries

edit /etc/mysql
$ sudo nano /etc/mysql/my.cnf

uncomment on line 72-73 general_log_file and general_log:

# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file = /var/log/mysql/mysql.log
general_log = 1

restart mysql:
$ sudo /etc/init.d/mysql restart

view the log:
$ tail -f /var/log/mysql/mysql.log

Data Log

When building a site or managing a server its very beneficial to look at data and error logs.

In WAMP:
To check if php error logs is enabled run phpinfo() and under Core table and the error_log row if ‘no value’ it means its not enabled. To enable PHP error logging open C:\wamp\bin\apache\Apache2.2.17\bin\php.ini and uncomment error_log line. See below

; Log errors to specified file. PHP's default behavior is to leave this value
; empty.
; http://php.net/error-log
; Example:
;error_log = php_errors.log
; Log errors to syslog (Event Log on NT, not valid in Windows 95).
;error_log = syslog
error_log = "c:/wamp/logs/php_error.log"

Reference

Ubuntu Server:
PHP error logs is enabled and shows up /var/log/apache2/error.log

For MySQL logging uncomment the general_log_file and general_log line in /etc/mysql/my.cnf see example below

# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
general_log_file        = /var/log/mysql/mysql.log
general_log             = 1

log_error                = /var/log/mysql/error.log

Then restart MySQL
$ /etc/init.d/mysql restart

Reference

Search in all fields from every table in a database

Sometimes we need to find out a small piece of string in big Database. Like where is the configuration saved, or where is Jon’s birth date saved. This PHP code can search all the tables, all the rows and columns in a MYSQL Database. To download

OR

If you have phpMyAdmin installed use its ‘Search’ feature.

  • Select your DB
  • Be sure you do have a DB selected (i.e. not a table, otherwise you’ll get a completely different search dialog)
  • Click ‘Search’ tab
  • Choose the search term you want
  • Choose the tables to search

tags:
MySQL – Search in all fields from every table from a database
search all records mysql
anywhereindb.php

MySQL Errors

ERROR 2002

server1@server1:~$ mysql -uroot -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Solution:
Installed MySQL server

$ sudo apt-get install mysql-server

If in windows:
getting this error maybe because mysqld is not running. In cmd run,
$ mysqld

to check if its running you can see if the you’re listening to the port 3306
$ netstat -a -n

to check if its running you can telnet it
$ telnet localhost 3306

A useful tool
$ mysqladmin

ERROR 1709 / ERROR 1071

http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/

ERROR 1709 (HY000) at line 206: Index column size too large. The maximum column size is 767 bytes.

mysql error 1709

Show mysql variables
mysql> show variables
mysql> show variables where variable_name = 'innodb_large_prefix'
mysql> SELECT @@innodb_large_prefix

View table file_format and row_format
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES where name like '%database_name/table_name%'

Make changes to variables (but you should also set them in my.cnf so they survive a restart)
mysql> set global innodb_file_format = BARRACUDA;
mysql> set global innodb_large_prefix = ON;

Change table row format after table has been created
mysql> ALTER TABLE tablename ROW_FORMAT=DYNAMIC

Specify table row format when table is being created
...
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

In InnoDB version 5.7.9 you can set a default row format