Archive for the ‘Mysql’ Category

Assalamualaikum,

Aida saya dah jumpa cara senang nak recover root password utk freebsd @ Linux :
Step 1 = Stop mysql
 
Freebsd = /usr/local/rc.d/mysql-server stop
Ubuntu / Debian = /etc/init.d/mysql stop
 
Step 2 = start mysql data 
 
Freebsd : /usr/local/rc.d/mysql-server –skip-grant-tables &
Ubuntu / Debian = /usr/sbin/mysqld –skip-grant-tables –skip-networking &
 
Step 3 = Login Mysql
 
Freebsd = mysql -u root mysql
Ubuntu / Debian = mysql -u root
 
Step 4 : Change Password
 
** Utk root yg bleh access database online i.e.:  Muamalat
 
mysql> FLUSH PRIVILEGES;
mysql> UPDATE mysql.user SET Password=PASSWORD(‘newpwd’) WHERE User=‘root’;
 
** Utk Mysql biasa i.e: Arrahn
 
mysql> FLUSH PRIVILEGES;
mysql> SET PASSWORD FOR root@‘localhost’ = PASSWORD(‘password’);
 
Step 5 : kill mysql
 
killall mysqld 
 
issue command ni “ps aux | grep mysql”
dapatkan PID kemudian kill PID “killall PID”
 
CTH : 

msyamsuris-iMac:~ msyamsuri$ ps aux | grep mysql

msyamsuri        2866   0.0  0.0  2432772    664 s000  S+    8:08PM   0:00.00 grep mysql

2866 <– PID No

Step 6 : hidupkan semula mysql

Freebsd = /usr/local/rc.d/mysql-server strt
Ubuntu / Debian = /etc/init.d/mysql start

OK sekian utk Tutorial . 

** Email to mysq X-staff fine simple solutions for mysql root recovery.

Advertisements

Mysql huge databases Server, cut n paste

———————–cut here —————————–

[mysqld]
local-infile=0
skip-locking
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency=2
myisam_sort_buffer_size=64M
server-id=1
skip-innodb
skip-bdb
log-slow-queries
skip_name_resolve

[safe_mysqld]
err-log=/var/log/mysqld.log
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

———————–cut here —————————–

Mysql-Proxy Guide on Centos

Posted: October 23, 2013 in Mysql

# su –

#Install EPEL
## Step 1
wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
wget http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
rpm -Uvh remi-release-6*.rpm epel-release-6*.rpm

## Step 2
ls -1 /etc/yum.repos.d/epel* /etc/yum.repos.d/remi.repo
/etc/yum.repos.d/epel.repo
/etc/yum.repos.d/epel-testing.repo
/etc/yum.repos.d/remi.repo

## Step 3
nano /etc/yum.repos.d/remi.repo

## Step 4
[remi]
name=Les RPM de remi pour Enterprise Linux $releasever – $basearch
#baseurl=http://rpms.famillecollet.com/enterprise/$releasever/remi/$basearch/
mirrorlist=http://rpms.famillecollet.com/enterprise/$releasever/remi/mirror
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-remi
failovermethod=priority

## Step 5
yum update -y

## Step 6
yum install mysql-proxy

## Step 7
nano /etc/default/mysql-proxy

#– cut here —
ENABLED=”true”
OPTIONS=”–defaults-file=/etc/mysql-proxy.cnf”
#– cut here —

## Step 8
nano /etc/mysql-proxy.cnf

#– cut here —

[mysql-proxy]
daemon = true
proxy-address = xxx.yyy.ccc.bbb:3306
proxy-skip-profiling = true
keepalive = true
event-threads = 50
pid-file = /var/run/mysql-proxy.pid
log-file = /var/log/mysql-proxy.log
log-level = debug
proxy-backend-addresses = 192.168.88.5:3306
proxy-lua-script=/usr/lib64/mysql-proxy/lua/proxy/auto-config.lua

#– cut here —

## Step 9
chmod 06660 /etc/mysql-proxy.cnf

## Step 10
nano /etc/rc.local

#– cut here —

mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

#– cut here —

## Step 11
System — Administration — Firewall — Other Ports — Add — 3306 tcp — Apply

## Step 12
/etc/init.d/sshd restart

## Step 13
ps aux | grep mysql-proxy

## Step 14
nano /etc/hosts

XXX.XXX.XXX.XXX codebranch.xxxx.com.my codebranch ***Contoh : 192.168.88.90 1303.xxxx.my 1303

##Step 14
hostname -f

## Step 15
hostname codebranch.xxxt.com.my *** Contoh hostname 1303.xxxx.com.my

##Step 14
hostname -f
** mesti sama dengan codebranch.xxxxx.com.my

 

Show Mysql Connections

Posted: November 23, 2011 in Mysql

Mysql show command status

mysql> SHOW STATUS;

mysql> SHOW GLOBAL STATUS;

mysql> show status like ‘Conn%’;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| Connections   | 8     |

+—————+——-+

1 row in set (0.00 sec)

 

mysql> show status like ‘%onn%’;

+————————–+——-+

| Variable_name            | Value |

+————————–+——-+

| Aborted_connects         | 0     |

| Connections              | 8     |

| Max_used_connections     | 4     |

| Ssl_client_connects      | 0     |

| Ssl_connect_renegotiates | 0     |

| Ssl_finished_connects    | 0     |

| Threads_connected        | 4     |

+————————–+——-+

7 rows in set (0.00 sec)

 

Mysql show process List

mysql> show processlist;

+—-+——+—————–+——–+———+——+——-+——————+

| Id | User | Host            | db     | Command | Time | State | Info             |

+—-+——+—————–+——–+———+——+——-+——————+

|  3 | root | localhost       | webapp | Query   |    0 | NULL  | show processlist |

|  5 | root | localhost:61704 | webapp | Sleep   |  208 |       | NULL             |

|  6 | root | localhost:61705 | webapp | Sleep   |  208 |       | NULL             |

|  7 | root | localhost:61706 | webapp | Sleep   |  208 |       | NULL             |

+—-+——+—————–+——–+———+——+——-+——————+

4 rows in set (0.00 sec)

 

Increasing Max MySQL Connections

[mysqld]

safe-show-database

innodb_data_file_path=ibdata1:10M:autoextend

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

max_connections=250

 

 

http://dev.mysql.com/doc/refman/5.0/en/show-status.html

http://kb.mediatemple.net/questions/711/(dv)+Increasing+Max+MySQL+Connections#dv

Sifat-sifat-Nya

Posted: August 22, 2011 in Mysql

“Allah itu nur bagi langit-langit dan bumi. Bandingan nur-Nya (adalah) seperti satu kurungan pelita yang di dalamnya ada pelita (sedang) pelita itu dalam satu kaca, (dan) kaca itu sebagai bintang yang seperti mutiara, yang dinyalakan (dengan minyak) dari pohon yang banyak faedah (iaitu) zaitun yang bukan bangsa timur dan bukan bangsa barat, yang minyaknya (sahaja) hampir menerangi, walaupun tidak disentuh api, nur atas nur, Allah pimpin kepada nur-Nya siapa yang Dia kehendaki, dan Allah mengadakan perumpamaan bagi manusia, dan Allah mengetahui tiap sesuatu”.

(Surah Nuur, ayat 35).

Optimizing and Tuning Your Database

Posted: August 20, 2011 in Mysql

4 thing you should know.

  • CPU—The faster the CPU, the faster MySQL will be able to process your data. There’s no real secret to this, but a 750 MHz processor is significantly faster than a 266 MHz processor. With processor speeds now over 1 GHz, and with reasonable prices all around, it’s not difficult to get a good bang for your buck.
  • Memory—Put as much RAM in your machine as you can. You can never have enough, and RAM prices will be at rock bottom for the foreseeable future. Having available RAM can help balance out sluggish CPUs.
  • Hard Drive—The proper hard drive will be both large enough and fast enough to accommodate your database server and its traffic. An important measurement of hard drive speed is its seek time, or the amount of time it takes for the drive to spin around and find a specific piece of information. Seek time is measured in milliseconds, and an average disk seek time is around 8 or 9 milliseconds. When buying a hard drive, make sure it’s big enough to accommodate all the data you’ll eventually store in your database and fast enough to find it quickly.
  • Operating System—If you use an operating system that’s a resource hog, you have two choices: buy enough resources (that is, RAM) so that it doesn’t matter, or use an operating system that doesn’t suck away all your resources just so that you can have windows and pretty colors. Also, if you are blessed with a machine with multiple processors, be sure your operating system can handle this condition and handle it well.

Using the benchmark() Function

A quick test of your server speed is to use the benchmark() MySQL function to see how long it takes to process a given expression. You can make the expression something simple, such as 10 + 10, or something more extravagant, such as extracting pieces of dates.

No matter the result of the expression, the result of benchmark() will always be 0. The purpose of benchmark() is not to retrieve the result of the expression but to see how long it takes to repeat the expression for a specific number of times. For example, the following command executes the expression 10 + 10 one million times:

mysql> SELECT BENCHMARK(1000000,10+10);

+—————————————-+
| BENCHMARK(1000000,10+10) |
+—————————————-+
|                            0 |
+—————————————-+
1 row in set (0.07 sec)

This command executes the date extraction expression, also one million times:

mysql> SELECT BENCHMARK(1000000, EXTRACT(YEAR FROM NOW()));

+———————————————————————–+
| BENCHMARK(1000000, EXTRACT(YEAR FROM NOW())) |
+———————————————————————–+
|                                                       0 |
+———————————————————————–+
1 row in set (0.06 sec)

The important number is the time in seconds, which is the elapsed time for the execution of the function. You may want to run the same uses of benchmark() multiple times during different times of day (when your server is under different loads) to get a better idea of how your server is performing.