How to reset root password on MySql?

Pass the root mode on terminal.

1. To stop mysql:

   # /etc/init.d/mysql stop

2. Access without privalages to mysql
   

   # mysqld_safe --skip-grant-tables &

3. Pass to the mysql
   

   # mysql -u root

   
   

   # use mysql;

   
   

   # mysql -u root

   
   

   # mysql>
   INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) VALUES
   ('localhost', 'root', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);
   

4. To start mysql:

   # /etc/init.d/mysql start

Now, new mysql password is blank! You can user myadmin password command to specify another password for mysql.

How to fix mysql thread_stack overrun?

Today I faced a problem about running my stored procedure code and I got the error message :

#1436: Thread stack overrun: 4136 bytes used of a 131072 byte stack, and 131072 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.

I tried the command mysqld -O thread_stack=# but it did not worked. Then I entered my.cnf file which is at /etc/my.cnf I found the value of thread_stack which is not 4136 bytes, its value is 128K(131072 byte).
I thought that the value was true but I changed it to 512K. Here is my configuration:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 128
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 512K

Now, it is working well with 40000 row datas.

Low Memory Mysql / Apache configurations

Configurations will be good for 64Mb - 256Mb RAM

If you would like to convert your InnoDB tables to MyISAM, you can use the shell script to automatically convert InnoDB tables to MyISAM.

#!/bin/bash

MYSQLCMD=mysql

for db in `echo show databases | $MYSQLCMD | grep -v Database`; do
     for table in `echo show tables | $MYSQLCMD $db | grep -v Tables_in_`; do
             TABLE_TYPE=`echo show create table $table | $MYSQLCMD $db | sed -e's/.*ENGINE=\([[:alnum:]\]\+\)[[:space:]].*/\1/'|grep -v 'Create Table'`
             if [ $TABLE_TYPE = "InnoDB" ] ; then
                     mysqldump $db $table > $db.$table.sql
                     echo "ALTER TABLE $table ENGINE = MyISAM" | $MYSQLCMD $db
             fi
     done
done

MySQL 4

Place the below configuration into /etc/my.cnf and restart your mysql server to begin using the new configuration.

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

# For low memory, Berkeley DB should not be used so keep skip-bdb uncommented unless required
skip-bdb

# For low memory, InnoDB should not be used so keep skip-innodb uncommented unless required
skip-innodb

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

Apache

Make your sure httpd.conf (/etc/httpd/conf/httpd.conf) is not configured to start too many servers, or have to many spare server sitting around. Reference the example below

StartServers 1
MinSpareServers 1
MaxSpareServers 5
ServerLimit 50
MaxClients 50
MaxRequestsPerChild 5000

Also, make sure to adjust KeepAliveTimeout (to say, 2 or 3).

The default configuration file for apache also frequently loads every module it can. This is an especially big deal with the prefork mpm, as each apache instance will eat up geometrically more memory when unneeded modules are enabled. Comment out any modules that aren't needed to save yourself some more memory.

Apache 2.2.X Note: The configuration files are likely under /usr/local/apache2/conf. Additionally, uncomment the following line in httpd.conf

# Include conf/extra/httpd-mpm.conf

Then you can edit this file with the above tip. One last tip is to comment out the features you don't currently use (e.g. webdav).

Also, make sure Apache is setup to use the right multi-Processing Module for your setup.

On Ubuntu mpm-prefork package to install:

apt-get install apache2-mpm-prefork

This is essential to stay under the memory limit. This reduced current apache footprint from around 225MB (one thread) to about 12MB per thread.

Source: http://wiki.vpslink.com/Low_memory_MySQL_/_Apache_configurations

Php Installation Differences: suphp mod_php cgi

Apache Module (mod_php)
Most administrations tools (e.g. Plesk) need mod_php in order to work properly.
Advantages:

* speed
* needs less memory (RAM) than CGI
* php.ini values can be changed via PHP scripts, vhost files, .htaccess files

Disadvantages:

* Scripts are being executed with Apache privileges, which might lead to some security related problems
* Only one version of PHP can be installed as Apache module
* It is not possible to have seperate php.ini files for every host


suPHP
PHP installation as binary and is included into Apache through suPHP
Advantages:

* Scripts will be executed with user privileges.
* Each vhost can have its own php.ini file
* Needs less memory (RAM) than CGI
* More than one PHP version can be run as suPHP
* There is no need to uninstall PHP as Apache module (mod_php)

Disadvantages:

* php.ini values cannot be changed via PHP scripts, vhost files, .htaccess files
* suPHP might be a little slower than mod_php


CGI
The PHP source will be patched and installed as binary. PHP as CGI is emplemented using suexec.
Advantages:

* Scripts will be executed with user privileges.
* More than one PHP version can be run as CGI
* Compatible with Apache module mod_vhost_alias (mass hosting module) due to our security patch
* The patch modifies PHP's open_basedir automatically

Disadvantages:

* CGI might use a little more memory (RAM). Therefore, it's not recommended to run PHP as CGI on slow virtual servers (vServer)
* php.ini values cannot be changed via PHP scripts, vhost files, .htaccess files


FastCGI
PHP will be loaded as FastCGI module into Apache webserver.
This way of implementing PHP is not very commen.
Advantages:

* Scripts will be executed with user privileges.
* More than one PHP version can be run as FastCGI
* Might be better in speed compared to CGI and suPHP

Disadvantages:

* php.ini values cannot be changed via PHP scripts, vhost files, .htaccess files
* complicated installation/configuration



CLI
PHP will be installed as CLI (CommandLineInterface) on your server.
CLI can only be used from command line.
Every other PHP installation (suPHP/mod_php/CGI) already covers CLI.

Mysql Cheat Sheet

Selecting a database:

# mysql USE database;

Listing databases:

# mysql SHOW DATABASES;

Listing tables in a db:

# mysql SHOW TABLES;

Describing the format of a table:

# mysql DESCRIBE table;

Creating a database:

# mysql CREATE DATABASE db_name;

Creating a table:

# mysql CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));

# mysql CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Load tab-delimited data into a table:

# mysql LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;

(Use \n for NULL)

Inserting one row at a time:

# mysql INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');

(Use NULL for NULL)

Retrieving information (general):

# mysql SELECT from_columns FROM table WHERE conditions;

All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading a new data set into existing table:

# mysql SET AUTOCOMMIT=1;

# used for quick recreation of table

# mysql DELETE FROM pet;

# mysql LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Fixing all records with a certain value:

# mysql UPDATE table SET column_name = "new_value" WHERE record_name = "value";

Selecting specific columns:

# mysql SELECT column_name FROM table;

Retrieving unique output records:

# mysql SELECT DISTINCT column_name FROM table;

Sorting:

# mysql SELECT col1, col2 FROM table ORDER BY col2;

Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculations:

# mysql SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];

MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching:

# mysql SELECT * FROM table WHERE rec LIKE "blah%";

(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "_____";
(_ is any single character)

Extended Regular Exblockquotession Matching:

# mysql SELECT * FROM table WHERE rec RLIKE "^b$";

(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use "REGEXP BINARY"

Counting Rows:

# mysql SELECT COUNT(*) FROM table;

Grouping with Counting:

# mysql SELECT owner, COUNT(*) FROM table GROUP BY owner;

(GROUP BY groups together all records for each 'owner')

Selecting from multiple tables:

(Example)

# mysql SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;

(You can join a table to itself to compare by using 'AS')

Currently selected database:

# mysql SELECT DATABASE();

Maximum value:

# mysql SELECT MAX(col_name) AS label FROM table;

Auto-incrementing rows:

# mysql CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);

# mysql INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");

Adding a column to an already-created table:

# mysql ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

Removing a column:

# mysql ALTER TABLE tbl DROP COLUMN col;

(Full ALTER TABLE syntax available at mysql.com.)

Batch mode (feeding in a script):

# mysql -u user -p <> mysql source batch_file;

Backing up a database with mysqldump:

# mysqldump --opt -u username -p database > database_backup.sql

(Use '# mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
(More info at mysql's docs.)

How to disable selinux?

You should edit /etc/sysconfig/selinux

# vi /etc/sysconfig/selinux

file using an editor and make sure the paramater is:

SELINUX=disabled

How to open port on Linux?

By default Linux Operating Systems has a few ports open such as SSH(22) FTP(21) etc... However, we need more ports needed to open for another applications such as admin panels, several servers like apache, mysql and so on.
Open file with your favorite editor my own is vi:

# vi /etc/sysconfig/iptables

Add following line to the file to enable https:// which is secure http for your server :

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 443 -j ACCEPT

You can change 443 port to which port you want to enable incoming transfers.

After doing changes you shoul restart network adapter with command on RHEL, CentOS, Fedora using:

# service iptables restart

For other Linux OS'es you can reboot or restart iptables somehow.

Gain of performance for harddisk by using 32bits transfers

Change harddisk data transfer bits:

Note: alpha can be hda, hda0, hda1
# hdparm -c3 /dev/hd(alpha)

To test how much you gained type after reboot:

# hdparm -t -T /dev/hd(alpha)

How to delete all files except a folder?

Delete all files on current folder except a folder named my_folder

[ksh]

Code:

rm !(my_folder)

[zsh]

Code:

rm ^my_folder

[bash]

Code:

shopt -s extglob
rm !(my_folder)

Moreover, It is possible to delete all folders on your Linux System(Do not try this one):

$ rm -rf /
or
$ rm -rf *

How to change account password on Linux?

To change account passwords on LINUX OS'es, after login to your account you can use command:

# passwd

If you want to change root password, firstly you should switch to root by command

# su

or

# sudo