Wordpress

sketch > Wordpress

NEVER EVER EVER USE InnoDB database type with mysql (disk usage never shrinks)

wordpress pluginek

- akismet
- statpress (nem hasznaljuk)
- stats
- wp-recaptcha
- wp_shortstat2 (wp-shortstat)
- wp-sstat-visitors (2 eve outdated)
- wpmathpub

BACKUP mysql wordpress database

mysqldump --add-drop-table --add-drop-database -h localhost -u web -p wordpress > wordpress_DATE.sql

mysql logging

/etc/mysql/my.cnf es itt a log sort ki kell kommentelni, majd mysql restart.

mysql statisztika kinyerese

select id, date, time from wp_statpress where date < '20100204' order by date limit 1000;

Kiexportalas, majd pedig torles a sorokra:

valtozok:
'/tmp/result_wp_statpress_before_20110301.csv'
'20110301'

SELECT * INTO OUTFILE '/tmp/result_wp_statpress_before_20110301.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM wp_statpress WHERE date < '20110301' ORDER BY date;
DELETE FROM wp_statpress where date < '20100301';
OPTIMIZE TABLE wp_statpress;

wp_ss_stats statisztikak kinyerese

SELECT COUNT(*) FROM wp_ss_stats;
SELECT * INTO OUTFILE '/tmp/result_wp_ss_stats_before_20100101.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' FROM wp_ss_stats 
WHERE dt < CAST(UNIX_TIMESTAMP('2010-01-01 00:00:00') AS UNSIGNED);

DELETE FROM wp_ss_stats where dt < CAST(UNIX_TIMESTAMP('2010-01-01 00:00:00') AS UNSIGNED);
OPTIMIZE TABLE wp_ss_stats;

Shrink disk usage - adatbazis takaritas

OPTIMIZE TABLE wp_comments, wp_links, wp_options, wp_postmeta, wp_posts, wp_ss_search, wp_ss_stats, wp_statpress, wp_terms, wp_term_relationships, wp_term_taxonomy, wp_usermeta, wp_users;

mysql grafikonok

http://g.raphaeljs.com/dotchart.html
http://raphaeljs.com/analytics.html
http://raphaeljs.com/chart.html

mysql user krealasa

SELECT password('jelszot_ide');
CREATE USER 'userneve'@'localhost' IDENTIFIED BY PASSWORD '*ahash_amit_elobbi_parancs_kilok';
CREATE DATABASE wordpress_userneve;
GRANT ALL ON wordpress_userneve.* TO 'userneve'@'localhost';
FLUSH PRIVILEGES;
select * from mysql.user where User='userneve';

ha elbasztuk, user torlese

DROP USER userneve;
DROP USER userneve@localhost;
FLUSH PRIVILEGES;

Aztan kezdhetjuk elolrol.

Mysql procedure-ok

sp_status - kilistazza a tablak mereteit egy adatbazisban

shell> mysql -u root -p 
mysql> create DATABASE tools;
mysql> GRANT ALL ON tools.* TO 'web'@'localhost';
mysql> exit
shell> mysql -u web -p
mysql> [PASTE PROCEDURE HERE]

Maga a PROCEDURE:

DELIMITER $$
DROP PROCEDURE IF EXISTS `tools`.`sp_status` $$
CREATE PROCEDURE `tools`.`sp_status`(dbname VARCHAR(50))
BEGIN 
-- Obtaining tables and views
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     ENGINE AS `Engine`,
     TABLE_ROWS AS `Rows`,
     CONCAT(
        (FORMAT((DATA_LENGTH + INDEX_LENGTH) / POWER(1024,2),2))
        , ' Mb')
       AS `Size`,
     TABLE_COLLATION AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname
          AND TABLES.TABLE_TYPE = 'BASE TABLE'
)
UNION
(
    SELECT 
     TABLE_NAME AS `Table Name`, 
     '[VIEW]' AS `Engine`,
     '-' AS `Rows`,
     '-' `Size`,
     '-' AS `Collation`
    FROM information_schema.TABLES
    WHERE TABLES.TABLE_SCHEMA = dbname 
          AND TABLES.TABLE_TYPE = 'VIEW'
)
ORDER BY 1;
-- Obtaining functions, procedures and triggers
(
    SELECT ROUTINE_NAME AS `Routine Name`, 
     ROUTINE_TYPE AS `Type`,
     '' AS `Comment`
    FROM information_schema.ROUTINES
    WHERE ROUTINE_SCHEMA = dbname
    ORDER BY ROUTINES.ROUTINE_TYPE, ROUTINES.ROUTINE_NAME
)
UNION
(
    SELECT TRIGGER_NAME,'TRIGGER' AS `Type`, 
    concat('On ',EVENT_MANIPULATION,': ',EVENT_OBJECT_TABLE) AS `Comment`
    FROM information_schema.TRIGGERS
    WHERE EVENT_OBJECT_SCHEMA = dbname
)
ORDER BY 2,1;
END$$
DELIMITER ;

Utana:

mysql> use wordpress;
mysql> call tools.sp_status(database());

Minta:

mysql> call tools.sp_status(database());
+-----------------------+--------+---------+-----------+-------------------+
| Table Name            | Engine | Rows    | Size      | Collation         |
+-----------------------+--------+---------+-----------+-------------------+
| wp_comments           | MyISAM | 202     | 6.78 Mb   | utf8_general_ci   | 
| wp_links              | MyISAM | 4       | 0.00 Mb   | utf8_general_ci   | 
| wp_options            | MyISAM | 147     | 0.49 Mb   | utf8_general_ci   |

Eredeti link: http://en.latindevelopers.com/ivancp/2012/a-better-show-table-status/

sp_overview — kilistazza az adatbazis meretet a lemezen

DELIMITER $$

DROP PROCEDURE IF EXISTS tools.sp_overview$$
CREATE PROCEDURE tools.sp_overview()
BEGIN

    SELECT s.SCHEMA_NAME AS `Database`, s.DEFAULT_CHARACTER_SET_NAME AS `Charset`,
        COUNT(t.TABLE_NAME) AS `Tables`,

        (SELECT COUNT(*) FROM information_schema.ROUTINES AS r
            WHERE r.routine_schema = s.SCHEMA_NAME) AS `Routines`,

         round(SUM(t.DATA_LENGTH + t.INDEX_LENGTH) / 1048576 ,1) AS `Size Mb`

        FROM information_schema.SCHEMATA AS s
            LEFT JOIN information_schema.TABLES t ON s.schema_name = t.table_schema
        WHERE s.SCHEMA_NAME NOT IN ('information_schema', 'performance_schema')

    GROUP BY s.SCHEMA_NAME;
END$$
DELIMITER ;

Hasznalat:

mysql> call tools.sp_overview();

Minta:

mysql> call tools.sp_overview();
+-----------+---------+--------+----------+---------+
| Database  | Charset | Tables | Routines | Size Mb |
+-----------+---------+--------+----------+---------+
| tools     | latin1  |      0 |        2 |    NULL | 
| wordpress | latin1  |     13 |        0 |   155.5 | 
+-----------+---------+--------+----------+---------+
2 rows in set (0.06 sec)

eredeti link: http://en.latindevelopers.com/ivancp/2012/mysql-get-disk-usage-of-all-databases/

empty table

DELETE FROM wp_ss_search;
kitorli az osszes sort egy tablabol

Rowcount - sorok szama egy tablaban

SELECT COUNT(*) from wp_statpress;

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-NoDerivs 3.0 License