mysql安装
Linux下安装MySQL数据库
一、 下载安装包
1) wget http://www.cmake.org/files/v2.8/cmake-2.8.4.tar.gz
2) wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.15.tar.gz
二、 安装必要工具
1) yum install gcc
2) yum install gcc-c++
3) yum install ncurses-devel
4) yum install bison
三、 安装cmake
1) tar -zxv -f cmake-2.8.4.tar.gz
2) cd cmake-2.8.4
3) ./configure
4) make
5) make install
四、 创建mysql的安装目录及数据库存放目录
1) mkdir -p /usr/local/mysql //mysql安装目录
2) mkdir -p /usr/local/mysql/db_log //日志目录
3) mkdir -p /usr/local/mysql/data //mysql数据库存放目录
五、 创建mysql用户及用户组
1) groupadd mysql
2) useradd -r -g mysql mysql
六、 安装MySQL
1) tar -zxv -f mysql-5.6.15.tar.gz
2) cd mysql-5.6.15
3) cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1
4) make
5) make install
6) **************************************************************************************
7) 参数说明：
8) -DCMAKE_INSTALL_PREFIX=/usr/local/mysql //安装目录
9) -DINSTALL_DATADIR=/usr/local/mysql/data //数据库存放目录
10) -DDEFAULT_CHARSET=utf8 //使用utf8字符
11) -DDEFAULT_COLLATION=utf8_general_ci //校验字符
12) -DEXTRA_CHARSETS=all //安装所有扩展字符集
13) -DENABLED_LOCAL_INFILE=1 //允许从本地导入数据
14) **************************************************************************************
15) 注意事项：重新编译时，需要清除旧的对象文件和缓存信息。
16) make clean
17) rm -f CMakeCache.txt
18) rm -rf /etc/my.cnf
七、 配置
1) cd /usr/local/mysql
2) chown -R root:mysql . //把当前目录中所有文件的所有者所有者设为root，所属组为mysql
3) 或者：chown -R mysql:mysql .
4) chown -R mysql:mysql data
5) chown -R mysql:mysql db_log
6) cp support-files/my-medium.cnf /etc/my.cnf //将mysql的启动服务添加到系统服务中
八、 配置my.cnf文件
1) vi /etc/my.cnf
2) 修改datadir=/usr/local/mysql/data
3) 设置端口port = 9966
4) 设置其他配置选择，请参考线上数据库配置或附件my.cnf
附件my.cnf：
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL. [client] port=9966 [mysql] default-character-set=utf8 [mysqld] binlog_cache_size = 1M binlog_format = mixed character_set_server = utf8 datadir = /mysql/mysqld/data default-storage-engine = innodb federated = on thread_cache_size = 64 expire_logs_days = 15 log_bin_trust_function_creators = 1 log-bin= /mysql/mysqld/data_log/mysql-bin log-bin-index= /mysql/mysqld/data_log/mysql-bin.index max_binlog_size = 100M local_infile = off max_allowed_packet = 100M max_connections = 2000 max_connect_errors = 5000 max_seeks_for_key = 10 max_heap_table_size = 64M tmpdir = /mysql/mysqld/data tmp_table_size=64M pid-file = /mysql/mysqld/data/mysqld.pid port = 9966 socket= /mysql/mysqld/mysql.sock user=mysql slow_query_log = 1 long_query_time = 4 slow_query_log_file = /mysql/mysqld/data/mysql-slow.log transaction_isolation = READ-COMMITTED skip_name_resolve event_scheduler=ON max_allowed_packet=8M back_log=80 open_files_limit=5000 wait_timeout= 10 #skip_networking = off join_buffer_size = 8M query_cache_size = 100M sort_buffer_size = 8M sync_binlog = 16 max_write_lock_count = 10 key_buffer_size = 64M myisam_max_sort_file_size = 10G myisam_sort_buffer_size = 64M read_buffer_size = 2M read_rnd_buffer_size= 8M #fast_index_creation = on #innodb_adaptive_checkpoint = 2 innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 40G innodb_data_file_path = ibdata1:256M:autoextend innodb_data_home_dir = /mysql/mysqld/data #innodb_doublewrite = on innodb_file_format = Barracuda #innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_file_per_table = 1 innodb_log_buffer_size = 8M innodb_log_files_in_group = 3 innodb_log_file_size = 512M innodb_thread_concurrency = 24 innodb_stats_on_metadata = off innodb_online_alter_log_max_size=2G innodb_flush_log_at_trx_commit=1 innodb_autoextend_increment=64M #server-id = 1 #master-host=183.134.105.104 #master-user=slave #master-password=slave #master-port=9966 #master-connect-retry=30 #log-slave-updates #slave-net-timeout=60 #auto_increment_increment=2 #auto_increment_offset=1 #binlog-ignore-db=mysql #master-password=slave #master-port=9966 #master-connect-retry=30 #log-slave-updates #slave-net-timeout=60 #auto_increment_increment=2 #auto_increment_offset=1 #binlog-ignore-db=mysql # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. # basedir = ..... # datadir = ..... # port = ..... # server_id = ..... # socket = ..... # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
九、 创建系统数据库的表
1) cd /usr/local/mysql
2) scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
十、 设置环境变量
1) vi /root/.bash_profile
2) 修改PATH=$PATH:$HOME/bin为：PATH=$PATH:$HOME/bin:/usr/local/mysql/bin:/usr/local/mysql/lib
3) source /root/.bash_profile
十一、 将mysql的启动服务添加到系统服务中
1) cp support-files/mysql.server /etc/init.d/mysql
十二、 设置软连接
1) ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock（否则会报错）
十三、 修改MySQL的root用户的密码以及打开远程连接
1) mysql -u root mysql
2) use mysql;
3) GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY "root"; //为root添加远程连接的权限
4) update user set Password = password('root') where User='root';
5) flush privileges;
6) exit
7) mysql -u root -p登录测试
8) 备注：若还不能进行远程连接，则关闭防火墙，/etc/rc.d/init.d/iptables stop
十四、 附创建数据库账号脚本（仅拥有远程登录和执行存储过程权限）
1) mysql -u root -p
2) create user user_link@'%';
3) use mysql;
4) update user set Password = password('HJDAK8dsd2Dcp') where user = 'user_link';
5) GRANT USAGE ON *.* TO 'user_link'@'%' WITH MAX_QUERIES_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
6) GRANT EXECUTE ON `KTV`.* TO 'user_link'@'%';
7) GRANT EXECUTE ON `LIVE`.* TO 'user_link'@'%';
8) flush privileges;
mysql>grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对数据库vtdc的employee表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为123。
mysql>grant all privileges on vtdc.* to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对数据库vtdc所有表进行所有操作的权限，并设定口令为123。
mysql>grant all privileges on *.* to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
mysql>grant all privileges on *.* to joe@localhost identified by ‘123′;
给本机用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
