MySQL

Hi,

1. Ubuntu:
• Install:
• sudo apt-get install mysql-server
• Reinstall:
• Tricks:
• Allow others host access:
• mysql -uroot -p
• CREATE USER 'root'@'host-ip' IDENTIFIED BY 'password';
• GRANT ALL PRIVILEGES ON *.* TO 'root'@'host-ip' WITH GRANT OPTION;
• CREATE USER 'root'@'%' IDENTIFIED BY 'password';
• GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
• FLUSH PRIVILEGES;
• sudo vim /etc/mysql/my.cnf
• #bind-address           = 127.0.0.1
• sudo /etc/init.d/mysql restart
• Make sure your IP is correct
• Too many connections:
• Checking your config file /etc/mysql/my.cnf:
• max_connections: comment this line or just set value is 100
• [mysqld] - connect-timeout: set value is 5 or less
• Can not startup MySQL:
• Check security-related processes on your server:
• iptables
• AppArmor
• Try disabled profile for MySQL:
• Move /etc/apparmor.d/usr.sbin.mysqld to /etc/apparmor.d/disable/usr.sbin.mysqld
• Restart AppArmor
• SELinux
• etc
• Check all log files in /var/log
• I was focused on daemon.log
• Restore with large database dump file:
• Option 1:
• mkdir splits
• split -n 200 database_backup.sql splits/sql_
• This produced several dozen files in order, and it took about 10 minutes. The -n option told split to split each file up into 200 lines each. So the files were then named sql_aa, sql_ab, sql_ac all the way to sql_fg. Then, I did the following command using cat to pipe the files to mysql:
• cd splits
• cat sql_* | mysql -u root -p database_name
• Option 2:
• You need to ramp up your bulk_insert_buffer_size. The default is 8M. To see what your current setting is, run this:
• mysql> show variables like 'bulk%';
• Just add this to /etc/my.cnf
• [mysqld]
• bulk_insert_buffer_size=512M
• So as to not restart mysql, run this SQL command:
• SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 512;
• Try restore the dump file
• Option 3:
• Change in my.cnf file:
• [InnoDB]
• InnoDB_fast_shutdown = off
• [networking]
• [data/memory size]
• max_allowed_Packet = 100MB
• [timeout settings]
• connect_timeout = 60 (seconds)
• interactive_timeout = 57600
• [advanced]
• max_connections = 200
• Try restore the dump file
• Reset root password:
• sudo /etc/init.d/mysqld stop
• mysqld_safe --skip-grant-tables
• mysql --user=root mysql
• UPDATE user SET Password=PASSWORD('your-new-password') WHERE user='root';
• FLUSH PRIVILEGES;
• exit;
• killall mysqld
• sudo /etc/init.d/mysqld restart
• Replication:
• Master server:
• sudo vim /etc/mysql/my.cnf
• [mysqld]
• log-bin = /var/log/mysql/mysql-bin.log
• binlog-do-db=exampledb #chang exampledb to your db
• server-id=1
• 
  
• #skip-networking
• #bind-address            = 127.0.0.1
• sudo service mysql restart
• mysql -uroot -p
• Allow all host login to master server:
• GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY 'yourpassword'
• Allow single host login to master server
•  REPLICATION SLAVE ON *.* TO 'root'@'Slave-IP' IDENTIFIED BY 'yourpassword'
• FLUSH PRIVILEGES;
• USE exampledb
• FLUSH TABLES WITH READ LOCK;
• SHOW MASTER STATUS;
• Transfer database from master to slaves:
• Backup and restore database from master server to all Slaves
• Use "LOAD DATA FROM MASTER;"
• UNLOCK TABLES;
• Slave servers:
• sudo vim /etc/mysql/my.cnf
• [mysqld]
• server-id=2 #You can type other number id
• sudo service mysql restart
• mysql -uroot -p
• SLAVE STOP
• CHANGE MASTER TO
• MASTER_HOST='Master-IP/Master-domain',
• MASTER_USER='slave_user',
• MASTER_PASSWORD='<some_password>',
• MASTER_LOG_FILE='mysql-bin.006',
• You can get this file from master "SHOW MASTER STATUS" -> File
• MASTER_CONNECT_RETRY=10,
• MASTER_PORT=3306,
• MASTER_LOG_POS=183;
• You can get this file from master "SHOW MASTER STATUS" -> Position
• SHOW SLAVE STATUS
• mysqladmin -uroot -p shutdown
• mysqladmin -uroot -p start
• Slave Started
• InnoDB performance:
• Update this config to my.cnf
• innodb_buffer_pool_size=8GB
• innodb_log_file_size=256M
• innodb_log_buffer_size=4M
• innodb_flush_log_at_trx_commit=2
• innodb_thread_concurrency=8
• innodb_flush_method=O_DIRECT
• http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
• Before restart mysql, delete all old logs in "/var/lib/mysql":
• ib_logfile*
2. Centos:
• Install:
• yum install mysql-server
• yum install mysql
• yum install mysql-devel
• yum install php-mysql
• Reinstall:
• yum remove mysql-server
• yum remove mysql
• Tricks:
• By default mysql has no password. For change the password, just using these commands:
• mysql
• mysql> USE mysql;
• mysql> UPDATE user SET Password=PASSWORD('new-password') WHERE user='root';
• mysql> FLUSH PRIVILEGES;

Good luck!